Báo cáo thực tập Phân tích và thiết kế hệ thống quản lý cơ sở môn Nhập môn công nghệ thông tin và truyền thông | Trường Đại học Bách Khoa Hà Nội

MỤC LỤC
CHƯƠNG 1: ĐẶC TẢ HỆ THỐNG....................................................................3
1.1. Mục đích, yêu cầu.......................................................................................3
1.2. Mô tả hệ thống.............................................................................................3
1.3. Đặc tả các chức năng cần xây dựng...........................................................4
CHƯƠNG 2: PHÂN TÍCH DỮ LIỆU HỆ THỐNG...........................................5
2.1. Mô tả hệ thống dưới dạng mối quan hệ dữ liệu.......................................5
2.2. Xây dựng mô hình liên kết thực thể..........................................................5
2.2.1. Xác định tập thực thể và thuộc tính.......................................................5
2.2.2. Xác định các mối quan hệ......................................................................5
2.3. Cách chuyển đổi từ ER sang mô hình quan hệ........................................7
2.4. Phân tích nhu cầu sử dụng thông tin........................................................8
2.5. Chuẩn hóa mô hình quan hệ......................................................................8
2.5.1. Dạng chuẩn 1NF....................................................................................8
2.5.2. Dạng chuẩn 2NF....................................................................................9
2.5.3. Dạng chuẩn 3NF...................................................................................9
2.6. Đặc tả các bảng dữ liệu.............................................................................11
CHƯƠNG 3: CÀI ĐẶT VÀ KHAI THÁC CƠ SỞ DỮ LIỆU.........................13
3.1. Cài đặt Cơ sở dữ liệu Kho hàng..............................................................13
3.1.1. Tạo cở sở dữ liệu..................................................................................13
3.1.2. Nhập dữ liệu cho các bảng...................................................................13
3.2. Khai thác cơ sở dữ liệu.............................................................................15
3.2.1. Đại số quan hệ......................................................................................15
3.2.2. Truy vấn cơ bản...................................................................................16
3.2.3. Truy vấn nâng cao................................................................................17
3.2.4. Lập trình T-SQL...................................................................................18
CHƯƠNG 4: CÀI ĐẶT ỨNG DỤNG................................................................22
4.1. Kiến trúc menu hệ thống..........................................................................22
4.2. Giao diện các chức năng...........................................................................22
4.2.1 Giao diện đăng nhập.............................................................................22
4.2.2. Giao diện Home...................................................................................23
4.2.3. Giao diện nhập hàng............................................................................23
4.2.4. Giao diện xuất hàng.............................................................................24
4.2.5. Giao diện quản lý sản phẩm.................................................................24
4.2.6. Giao diện quản lý nhà cung cấp...........................................................25
4.2.7. Giao diện quản lý đại lý.......................................................................25
4.2.8. Giao diện quản lý phiếu nhập..............................................................26
4.2.9. Giao diện chi tiết phiếu nhập...............................................................26
4.2.10. Giao diện quản lý phiếu xuất.............................................................27
4.2.11. Giao diện chi tiết phiếu xuất..............................................................27
4.2.12. Giao diện quản lý nhân viên..............................................................28
4.2.13. Giao diện thông tin nhân viên............................................................28
CHƯƠNG 1: ĐẶC TẢ HỆ THỐNG
Yêu cầu: Một kho tổng lưu trữ các sản phẩm, kho tổng này có một số nhân viên đảm
nhiệm việc nhập hàng từ nhà cung cấp về kho. Các sản phẩm được quản lý theo nhóm sản
phẩm, mỗi sản phẩm đều được quản lý số lượng một cách chặt chẽ. Căn cứ vào số lượng này
người quản lý kho tổng có kế hoạch nhập hàng về kho. Kho tổng sẽ nhập hàng từ các nhà cung
cấp khác nhau. Mỗi phiếu nhập sẽ nhập nhiều mặt hàng nhưng nhập trọn vẹn từ một nhà cung cấp.
1.1. Mục đích, yêu cầu
Trong thời buổi công nghệ số hiện nay, nhiều quy trình, công đoạn hay các hệ thống
quản trị đều được mã hóa và vận hành bởi các thiết bị, phần mềm nhằm giúp doanh nghiệp,
cửa hàng, cơ sở kinh doanh... đạt được hiệu suất làm việc tốt nhất. Trên cơ sở đó, các hệ thống
quản trị cơ sở dữ liệu ra đời và đóng vai trò quan trọng trong xử lý và kiểm soát nguồn thông
tin. Do vậy, công tác quản lý hàng hoá là một công tác không thể thiếu của tất cả các tổ chức
về kinh tế. Thông tin về dữ liệu quản lý thay đổi hàng ngày, hàng giờ mà sổ sách không thể
cập nhật những thông tin đó một cách chính xác được. Vì vậy, xây dựng một hệ thống thông
tin quản lý các phiếu xuất, nhập trong các hệ thống kho hàng giúp người quản lý nắm bắt được
tình hình về số lượng sản phẩm xuất, nhập cũng như là số lượng sản phẩm tồn kho giúp công
tác quản lý nhanh gọn, hiệu quả hơn. Bài toán đặt ra là kho tổng A cần giảm đi công việc lưu
trữ thông tin bằng sổ sách cũng như tính toán lượng sản phẩm xuất, nhập và tồn kho mà họ đang quản lý. -
Thống kê số lượng hàng tồn kho. -
Mỗi sản phẩm được phân nhóm và quản lý theo mã riêng biệt. -
Thông tin về quá trình nhập hàng do nhân viên thực hiện với nhà cung cấp. -
Thông tin chi tiết về số lượng sản phẩm nhập trong từng phiếu nhập, số tiềnthanh toán…
Yêu cầu được đề ra: xây dựng cơ sở dữ liệu cho kho tổng A về quản lý sản phẩm trong
kho một cách hiệu quả, nhanh chóng và chính xác nhất.
1.2. Mô tả hệ thống
Kho tổng A sẽ quản lý số lượng hàng trong kho, các sản phẩm được phân thành các
nhóm và được quản lý theo các mã riêng biệt. Việc nhập hàng vào kho được giao cho các nhân
viên thực hiện với phía nhà cùng cấp. Sau mỗi lần thực hiện nhập sản phẩm, thông tin của
phiếu nhập sẽ được cập nhật vào hệ thống cơ sở dữ liệu của kho cũng như tính toán, cập nhật
những thay đổi của từng loại sản phẩm đang quản lý.
Để thiết kế cơ sở dữ liệu cho hệ thống quản lý kho tổng cần xác định các yêu cầu cụ thể
và mối quan hệ giữa các thực thể trong hệ thống. Dưới đây là mô tả chi tiết về các yêu cầu dữ liệu cần xậy dựng: -
Nhà cung cấp: Thông tin về các nhà cung cấp, bao gồm tên, địa chỉ, số điện
thoại, và các thông tin khác liên quan. -
Nhóm sản phẩm: Các sản phẩm được phân nhóm để dễ dàng quản lý. Mỗi nhóm
sản phẩm cần có tên nhóm và các thuộc tính khác liên quan. -
Sản phẩm: Thông tin về sản phẩm bao gồm: + Tên sản phẩm.
+ Số lượng tồn kho để theo dõi tình trạng hàng trong kho.
+ Liên kết với Nhóm sản phẩm để dễ dàng phân loại. -
Nhân viên: Thông tin về các nhân viên thực hiện việc nhập hàng, bao gồm tên,
địa chỉ, số điện thoại, và các thông tin liên quan. -
Phiếu nhập hàng:
+ Mỗi lần nhập hàng từ một nhà cung cấp sẽ được ghi lại trong một phiếu nhập hàng.
Thông tin về ngày nhập hàng, nhà cung cấp, nhân viên thực hiện.
+ Liệt kê danh sách các sản phẩm được nhập trong mỗi phiếu nhập hàng. Ghi lại số
lượng nhập, số tiền thanh toán và các thông tin khác liên quan đến từng sản phẩm trong phiếu nhập hàng.
Dựa vào những yêu cầu cụ thể trên ta tiến hành xây dựng mô hình dữ liệu để đáp ứng
tốt nhất cho nhu cầu quản lý kho tổng của mình.
1.3. Đặc tả các chức năng cần xây dựng -
Hệ thống phải quản lý chính xác số lượng sản phẩm hiện có trong kho theo từngmã sản phẩm. -
Sau khi thực hiện nhập, xuất kho hệ thống phải tự động cập nhật các thay đổi
vềsố lượng sản phẩm còn lại trong kho. -
Quản lý được việc xuất, nhập hàng mà từng nhân viên đã thực hiện, quản lý
thờigian xuất, nhập và số tiền thanh toán.
CHƯƠNG 2: PHÂN TÍCH DỮ LIỆU HỆ THỐNG
2.1. Mô tả hệ thống dưới dạng mối quan hệ dữ liệu -
Các sản phẩm trong kho được phân thành các nhóm sản phẩm, mỗi nhóm cónhiều
sản phẩm cùng loại. Mỗi sản phẩm chỉ thuộc một nhóm sản phẩm nhất định. -
Quá trình nhập hàng được tiến hành giữa nhân viên và nhà cung cấp. Việc
nhậphàng được quản lý bằng các phiếu nhập. Mỗi nhân viên hoặc nhà cung cấp có thể thực
hiện nhiều phiếu nhập khác nhau nhưng trong mỗi phiếu nhập chỉ do 1 nhân viên và 1 nhà cung cấp thực hiện. -
Trong mỗi phiếu nhập, có thể có nhiều sản phẩm khác nhau. Mỗi sản phẩm
cũngcó thể xuất hiện trong nhiều phiếu nhập.
2.2. Xây dựng mô hình liên kết thực thể
2.2.1. Xác định tập thực thể và thuộc tính
Hình 2.1: Xác định tập thực thể và thuộc tính
2.2.2. Xác định các mối quan hệ -
Quan hệ sản phẩm – nhóm sản phẩm: Một nhóm sản phẩm có nhiều sản
phẩm,một sản phẩm chỉ thuộc một nhóm sản phẩm (Quan hệ 1-n).
Hình 2.2: Quan hệ san pham – nhom san pham -
Quan hệ nhân viên – phiếu nhập: Một nhân viên có thể thực hiện nhiều
phiếunhập, một phiếu nhập chỉ do một nhân viên thực hiện (Quan hệ 1-n).
Hình 2.3: Quan hệ nhân viên – phiếu nhập -
Quan hệ nhà cung cấp – phiếu nhập: Một nhà cung cấp có thể thực hiện
nhiềuphiếu nhập, một phiếu nhập chỉ do một nhà cung cấp thực hiện (Quan hệ 1-n).
Hình 2.3: Quan hệ nhà cung cấp – phiếu nhập -
Quan hệ phiếu nhập – sản phẩm: một phiếu nhập có thể có nhiều sản phẩm,
mộtsản phẩm có thể có mặt trong nhiều phiếu nhập (quan hệ n-n).
Hình 2.4: Quan hệ nhà phiếu nhập – sản phẩm
Sau khi xác định đầy đủ các mối quan hệ ta thu được mô hình liên kết thực thể:
Hình 2.5: Mô hình liên kết thực thể thu được 2.3.
Cách chuyển đổi từ ER sang mô hình quan hệ Thực hiện việc
chuyển đổi theo các quy tắc:
Quy tắc 1: Với kiểu liên kết 1:1 *
Cách 1: Chuyển khóa chính của LĐQH này sang làm khóa ngoại của LĐQH kia hoặc ngược lại. *
Cách 2: Nhập 2 kiểu thực thể và mối liên kết thành 1 LĐQH, chọn khóa chính cho phù hợp. -
Trong các mối quan hệ đã xác định theo yêu cầu không có kiểu liên kết 1:1.
Quy tắc 2: Với kiểu liên kết 1:n
Chuyển khóa chính của LĐQH bên 1 (cha) sang làm khóa ngoại của LĐQH bên nhiều (con). -
Quan hệ SanPham – NhomSanPham: Ta chọn mã nhóm của nhóm sản phẩm
làm khóa ngoại cho sản phẩm. Các lược đồ quan hệ thu được:
+ NhomSanPham(MaNhom, TenNhom)
+ SanPham(MaSanPham, TenSanPham, SoLuong, MaNhom) -
Quan hệ NhanVien – PhieuNhap: Ta chọn mã nhân viên của nhân viên làm
khóa ngoại cho phiếu nhập. -
Quan hệ NhaCungCap – PhieuNhap: Ta chọn mã công ty của nhà cung cấp
làm khóa ngoại cho phiếu nhập. Các lược đồ quan hệ thu được:
+ NhanVien(MaNV, HoVaTen, SDT, DiaChi)
+ NhaCungCap(MaCongTy, TenCongTy, SDT, DiaChi)
+ PhieuNhap(MaPhieu, ThoiGian, MaNV, MaCongTy)
Quy tắc 3: Với kiểu liên kết n:n
Chuyển mối liên kết thành một LĐQH có thuộc tính là thuộc tính của mối liên kết, thêm
các thuộc tính khóa chính của các LĐQH có liên quan, khóa chính của LĐQH mới này là các
thuộc tính mới thêm vào. -
Quan hệ SanPham – PhieuNhap: Ta tạo ra lược đồ quan hệ chi tiết phiếu nhập
với khóa chính là mã phiếu nhập và mã sản phẩm . Ta thu được lược đồ quan hệ:
+ ChiTietPhieuNhap(MaPhieuNhap, MaSanPham, SoLuongNhap)
2.4. Phân tích nhu cầu sử dụng thông tin -
Để đảm bảo về vấn đề bảo mật, phân quyền cụ thể cho từng nhân viên, tronglược
đồ quan hệ nhân viên ta bổ sung thêm các thuộc tính mật khẩu và quản lý. -
Để thuận tiện cho việc thống kê tiền nhập hàng ta thêm thuộc tính thành tiền
vàolược đồ chi tiết phiếu nhập.
* Sau khi phân tích ta thu được mô hình quan hệ:
- NhanVien(MaNV, HoVaTen, SDT, DiaChi, MatKhau, QuanLy)
- NhaCungCap(MaCongTy, TenCongTy, SDT, DiaChi)
- NhomSanPham(MaNhom, TenNhom)
- SanPham(MaSanPham, TenSanPham, SoLuong, MaNhom)
- PhieuNhap(MaPhieu, ThoiGian, MaNV, MaCongTy)
- ChiTietPhieuNhap(MaPhieuNhap, MaSanPham, SoLuongNhap, ThanhTien).
2.5. Chuẩn hóa mô hình quan hệ 2.5.1. Dạng chuẩn 1NF
* Tất cả các thuộc tính chỉ chứa giá trị nguyên tố.
+ NhanVien(MaNV, HoVaTen, SDT, DiaChi, MatKhau, QuanLy)
Không cần điều chỉnh, vì các thuộc tính chỉ chứa giá trị nguyên tố. +
NhaCungCap(MaCongTy, TenCongTy, SDT, DiaChi)
Không cần điều chỉnh, vì các thuộc tính chỉ chứa giá trị nguyên tố.
+ NhomSanPham(MaNhom, TenNhom)
Không cần điều chỉnh, vì các thuộc tính chỉ chứa giá trị nguyên tố. +
SanPham(MaSanPham, TenSanPham, SoLuong, MaNhom)
Không cần điều chỉnh, vì các thuộc tính chỉ chứa giá trị nguyên tố. +
PhieuNhap(MaPhieu, ThoiGian, MaNV, MaCongTy)
Không cần điều chỉnh, vì các thuộc tính chỉ chứa giá trị nguyên tố.
+ ChiTietPhieuNhap(MaPhieuNhap, MaSanPham, SoLuongNhap, ThanhTien) Không
cần điều chỉnh, vì các thuộc tính chỉ chứa giá trị nguyên tố. 2.5.2. Dạng chuẩn 2NF
* Mỗi bảng cần chuẩn hóa dạng 1NF. Tất cả các thuộc tính không khoá phụ thuộc
hàm đầy đủ vào khoá chính
+ NhanVien(MaNV, HoVaTen, SDT, DiaChi, MatKhau, QuanLy)
Không cần điều chỉnh, vì MaNV là khóa chính và các thuộc tính còn lại phụ thuộc hàm
đầy đủ vào khóa chính.
+ NhaCungCap(MaCongTy, TenCongTy, SDT, DiaChi)
Không cần điều chỉnh, vì MaCongTy là khóa chính và các thuộc tính còn lại phụ thuộc
hàm đầy đủ vào khóa chính.
+ NhomSanPham(MaNhom, TenNhom)
Không cần điều chỉnh, vì MaNhom là khóa chính và các thuộc tính còn lại phụ thuộc
hàm đầy đủ vào khóa chính.
+ SanPham(MaSanPham, TenSanPham, SoLuong, MaNhom)
Không cần điều chỉnh, vì MaSanPham là khóa chính và các thuộc tính còn lại phụ thuộc
hàm đầy đủ vào khóa chính.
+ PhieuNhap(MaPhieu, ThoiGian, MaNV, MaCongTy)
Không cần điều chỉnh, vì MaPhieu là khóa chính và các thuộc tính còn lại phụ thuộc
hàm đầy đủ vào khóa chính.
+ ChiTietPhieuNhap(MaPhieuNhap, MaSanPham, SoLuongNhap, ThanhTien)
Không cần điều chỉnh, vì (MaPhieuNhap, MaSanPham) là khóa chính và các thuộc tính
còn lại phụ thuộc hàm đầy đủ vào khóa chính. 2.5.3. Dạng chuẩn 3NF
* Mỗi bảng cần chuẩn hóa dạng 2NF. Mọi thuộc tính không khoá đều không phụ
thuộc bắc cầu vào khoá chính.
+ NhanVien(MaNV, HoVaTen, SDT, DiaChi, MatKhau, QuanLy)
Không cần điều chỉnh, vì không có thuộc tính phụ thuộc bắc cầu vào khóa chính.
+ NhaCungCap(MaCongTy, TenCongTy, SDT, DiaChi)
Không cần điều chỉnh, vì không có thuộc tính phụ thuộc bắc cầu vào khóa chính. +
NhomSanPham(MaNhom, TenNhom)
Không cần điều chỉnh, vì không có thuộc tính phụ thuộc bắc cầu vào khóa chính. +
SanPham(MaSanPham, TenSanPham, SoLuong, MaNhom)
Không cần điều chỉnh, vì không có thuộc tính phụ thuộc bắc cầu vào khóa chính. +
PhieuNhap(MaPhieu, ThoiGian, MaNV, MaCongTy)
Không cần điều chỉnh, vì không có thuộc tính phụ thuộc bắc cầu vào khóa chính. +
ChiTietPhieuNhap(MaPhieuNhap, MaSanPham, SoLuongNhap, ThanhTien) Không
cần điều chỉnh, vì không có thuộc tính phụ thuộc bắc cầu vào khóa chính. - Sau khi
chuẩn hóa ta thu được mô hình quan hệ sau:
Hình 2.6: Mô hình quan hệ thu được
2.6. Đặc tả các bảng dữ liệu
Các bảng dữ liệu đã xây dựng:
Bảng NhaCungCap: Lưu thông tin của công ty cung cấp sản phẩm. Thuộc tính Kiểu dữ liệu Độ dài Mô tả Ghi chú MaCongTy char 10 Mã công ty Khóa chính TenCongTy nvarchar 50 Tên công ty SDT char 20 SĐT công ty DiaChi nvarchar 100 Địa chỉ công ty
Bảng NhomSanPham: Lưu thông tin về các nhóm sản phẩm. Thuộc tính Kiểu dữ liệu Độ dài Mô tả Ghi chú MaNhom char 10 Mã nhóm SP Khóa chính TenNhom nvarchar 50 Tên nhóm SP
Bảng SanPham: Lưu thông tin của từng sản phẩm và phân nhóm sản phẩm. Thuộc tính Kiểu dữ liệu Độ dài Mô tả Ghi chú MaSanPham char 10 Mã sản phẩm Khóa chính TenSanPham nvarchar 50 Tên sản phẩm SoLuong int Số lượng tồn kho MaNhom nvarchar 100 Mã nhóm SP Khóa ngoại
Bảng NhanVien: Lưu thông tin cá nhân của từng nhân viên. Thuộc tính Kiểu dữ liệu Độ dài Mô tả Ghi chú MaNV char 10 Mã nhân viên Khóa chính HoVaTen nvarchar 50 Họ tên SDT char 20 SĐT NV DiaChi nvarchar 100 Địa chỉ NV MatKhau varchar 20 Mật khẩu QuanLy int Phân quyền NV
Bảng PhieuNhap: Lưu các thông tin về phiếu nhập. Mỗi phiếu nhập do 01 nhân viên
và 01 nhà cung cấp thực hiện. Thuộc tính Kiểu dữ liệu Độ dài Mô tả Ghi chú MaPhieu char 10 Mã phiếu Khóa chính ThoiGian date Ngày nhập MaNV char 10 Mã nhân viên Khóa ngoại MaCongTy char 10 Mã công ty Khóa ngoại
Bảng ChiTietPhieuNhap: Lưu các thông tin chi tiết về các sản phẩm có trong phiếu
nhập. Mỗi phiếu nhập có thể bao gồm nhiều sản phẩm. Thuộc tính Kiểu dữ liệu Độ dài Mô tả Ghi chú MaPhieuNhap char 10 Mã phiếu nhập Khóa chính MaSanPham date 10 Mã sản phẩm Khóa chính SoLuongNhap int Số lượng nhập ThanhTien float Tiền nhập SP
CHƯƠNG 3: CÀI ĐẶT VÀ KHAI THÁC CƠ SỞ DỮ LIỆU
3.1. Cài đặt Cơ sở dữ liệu Kho hàng
3.1.1. Tạo cở sở dữ liệu -- Tạo database KhoHang CREATE DATABASE KhoHang GO USE KhoHang GO -- Tạo bang NhanVien CREATE TABLE NhanVien (
MaNV NVARCHAR(50) PRIMARY KEY, HoVaTen NVARCHAR(100), SDT NVARCHAR(20), DiaChi NVARCHAR(100), MatKhau VARCHAR(20), QuanLy INT ); GO -- Tạo bang NhaCungCap CREATE TABLE NhaCungCap (
MaCongTy CHAR(10) PRIMARY KEY, TenCongTy NVARCHAR(100), SDT NVARCHAR(20), DiaChi NVARCHAR(100) ); GO -- Tạo bang NhomSanPham CREATE TABLE NhomSanPham ( MaNhom CHAR(10) PRIMARY KEY, TenNhom NVARCHAR(100) ); GO -- Tạo bang SanPham CREATE TABLE SanPham (
MaSanPham CHAR(10) PRIMARY KEY, TenSanPham NVARCHAR(100), SoLuong INT, MaNhom CHAR(10),
FOREIGN KEY (MaNhom) REFERENCES NhomSanPham(MaNhom) ); GO -- Tạo bang PhieuNhap CREATE TABLE PhieuNhap (
MaPhieu CHAR(20) PRIMARY KEY, ThoiGian DATETIME, MaNV NVARCHAR(50), MaCongTy CHAR(10),
FOREIGN KEY (MaNV) REFERENCES NhanVien(MaNV),
FOREIGN KEY (MaCongTy) REFERENCES NhaCungCap(MaCongTy)); GO
-- Tạo bang ChiTietPhieuNhap
CREATE TABLE ChiTietPhieuNhap ( MaPhieu CHAR(20), MaSanPham CHAR(10), SoLuongNhap INT, ThanhTien FLOAT,
PRIMARY KEY (MaPhieu, MaSanPham),
FOREIGN KEY (MaPhieu) REFERENCES PhieuNhap(MaPhieu),
FOREIGN KEY (MaSanPham) REFERENCES SanPham(MaSanPham));
3.1.2. Nhập dữ liệu cho các bảng
-- Thêm dữ liệu bang NhanVien
INSERT INTO NhanVien (MaNV, HoVaTen, SDT, DiaChi, MatKhau, QuanLy) VALUES
('NV001', N'Nguyê7n Trọng Đạt', '0123456789', N'Hà Nội', '%Co050296', 1),
('NV002', N'TrầEn Thị Bình', '0987654321', N'Hà Nội', '123456', 0),
('NV003', N'Lê Văn ChiêIn', '0369852147', N'Hà Nội', 'qwerty', 0),
('NV004', N'Phạm Thị Dung', '0321478963', N'Vĩnh Phúc', '123456', 0),
('NV005', N'Hoàng Văn Minh', '0157986423', N'Nam Định', 'abcdef', 0), GO
-- Thêm dữ liệu bang NhaCungCap
INSERT INTO NhaCungCap (MaCongTy, TenCongTy, SDT, DiaChi) VALUES
('NCC001', N'Vinamilk', '0123456789', 'BăIc Giang'),
('NCC002', N'Coca-Cola Việt Nam', '0451236789', 'HồE Chí Minh'),
('NCC003', N'Libby Việt Nam', '0369871234', 'Bình Dương'),
('NCC004', N'Acecook Việt Nam', '0321654978', 'HồE Chí Minh'),
('NCC005', N'Knorr Việt Nam', '0451236789', 'ĐồEng Nai'),
('NCC006', N'Cồng ty Cồ phầEn Nồng san Việt Nam', '0912345678', 'Hà Nội'),
('NCC007', N'Cồng ty Cồ phầEn XuầIt khầu RồEng Việt', '0369876543', 'TiêEn Giang'), GO
-- Thêm dữ liệu bang NhomSanPham
INSERT INTO NhomSanPham (MaNhom, TenNhom) VALUES ('NH001', N'Sữa'),
('NH002', N'ĐồE uồIng'),
('NH003', N'ĐồE hộp'), ('NH004', N'Mì gói'), ('NH005', N'Gia vị'), ('NH006', N'Rau cu qua'), ('NH007', N'Trái cầy'),
('NH008', N'Thực phầm khồ'),
('NH009', N'ĐồE dùng nhà bêIp'),
('NH010', N'Hóa phầm tầy rưa'); GO
-- Thêm dữ liệu bang SanPham
INSERT INTO SanPham (MaSanPham, TenSanPham, SoLuong, MaNhom) VALUES
('SP001', N'Sữa tươi Vinamilk', 100, 'NH001'),
('SP002', N'Nước ngọt Coca-Cola', 150, 'NH002'),
('SP003', N'Hộp cá ngừ ngầm dầEu', 80, 'NH003'),
('SP004', N'Mì gói Acecook', 120, 'NH004'),
('SP005', N'Nước măIm Maggi', 50, 'NH005'),
('SP006', N'Cà chua', 100, 'NH006'),
('SP007', N'Cam sành', 200, 'NH007'),
('SP008', N'Mộc nhĩ', 30, 'NH008'),
('SP009', N'Chao chồIng dính', 20, 'NH009'),
('SP010', N'Nước lau sàn Vim', 15, 'NH010'); GO
-- Thêm dữ liệu bang PhieuNhap
INSERT INTO PhieuNhap (MaPhieu, ThoiGian, MaNV, MaCongTy) VALUES
('PN001', '2024-03-01', 'NV001', 'NCC001'),
('PN002', '2024-03-05', 'NV002', 'NCC002'),
('PN003', '2024-03-08', 'NV003', 'NCC003'),
('PN004', '2024-03-10', 'NV004', 'NCC004'),
('PN005', '2024-03-12', 'NV005', 'NCC005'), GO
-- Thêm dữ liệu bang ChiTietPhieuNhap
INSERT INTO ChiTietPhieuNhap (MaPhieu, MaSanPham, SoLuongNhap, ThanhTien) VALUES
('PN001', 'SP001', 50, 250000),
('PN002', 'SP002', 30, 120000),
('PN003', 'SP003', 40, 160000),
('PN004', 'SP004', 60, 108000),
('PN005', 'SP005', 25, 75000),
Hình 3.1: Mô hình dữ liệu (Diagram)
3.2. Khai thác cơ sở dữ liệu 3.2.1. Đại số quan hệ
* Cho biết mã số nhân viên hoặc mã số nhà cung cấp được quản lý trong Database
của kho tổng
- Quan hệ: NhanVien, NhaCungCap
KQ MaNV(NhanVien) v MaCongTy(NhaCungCap)
* Tìm thông tin các nhà cung cấp đã từng cung cấp hàng cho cả 2 nhân viên A, B
(A, B đại diện cho Họ và tên của 2 nhân viên)
- Quan hệ: NhanVien, NhaCungCap, PhieuNhap
NhanVien_A MaNV( NhanVien.HovaTen=A(NhanVien)) (Lấy mã số nhân viên A) NhanVien_B
MaNV( NhanVien.HovaTen=B(NhanVien)) (Lấy mã số nhân viên B)
PN_A PhieuNhap ►◄MaNV = MaNV(NhanVien_A) (Các phiếu nhập của A)
PN_B PhieuNhap ►◄MaNV = MaNV(NhanVien_B) (Các phiếu nhập của B)
C MaCongTy(PN_A) ^ MaCongTy(PN_B) (MaCongTy A, B cùng lấy hàng) KQ NhaCungCap ►◄ C
* Cho biết các thông tin chi tiết của sản phẩm có số lượng nhập lớn nhất (chỉ xét
trong các bộ của quan hệ ChiTietPhieuNhap, không tính tổng số lượng nhập của các sản
phẩm có cùng MaSanPham). - Quan hệ: ChiTietPhieuNhap
B1 SoLuongNhap(ChiTietPhieuNhap) (Lấy số lượng nhập của các bộ trong quan hệ)
B2 ChiTietPhieuNhap x B1 (Tạo ra một quan hệ mới để so sánh)
B3 ChiTietPhieuNhap.SoLuongNhap < B1.SoLuongNhap(B2)
B4 ChiTietPhieuNhap.SoLuongNhap(B3)
B5 B1 – B4 (Phép trừ) (B3, B4, B5 tìm ra số lượng nhập lớn nhất) KQ ChiTietPhieuNhap ►◄ B5 3.2.2. Truy vấn cơ bản
--1.LầIy tầIt ca thồng tin cua bang NhanVien SELECT * FROM NhanVien;
--2.LầIy thồng tin các nhần viên có địa chỉ ơ Hà Nội
SELECT * FROM NhanVien WHERE DiaChi LIKE N'%Hà Nội%';
--3.LầIy thồng tin các nhà cung cầIp ơ HồE Chí Minh
SELECT * FROM NhaCungCap WHERE DiaChi LIKE N'%HồE Chí Minh%';
--4.LầIy thồng tin những nhần viên là quan lý
SELECT * FROM NhanVien WHERE QuanLy = 1;
--5.LầIy thồng tin các nhần viên theo thứ tự địa chỉ
SELECT * FROM NhanVien ORDER BY DiaChi ASC;
--6.LầIy tên san phầm theo từng nhóm san phầm
SELECT TenNhom, TenSanPham FROM SanPham
JOIN NhomSanPham ON SanPham.MaNhom = NhomSanPham.MaNhom ORDER BY TenNhom;
--7.ĐêIm sồI lượng các san phầm trong từng nhóm san phầm
SELECT TenNhom, COUNT(*) AS SoLuongSanPham FROM SanPham
JOIN NhomSanPham ON NhomSanPham.MaNhom = SanPham.MaNhom GROUP BY TenNhom ORDER BY SoLuongSanPham DESC;
--8.LầIy danh sách tên những san phầm được nhập vào bơi nhần viên có mã là 'NV001'
SELECT DISTINCT TenSanPham FROM SanPham
JOIN ChiTietPhieuNhap ON SanPham.MaSanPham = ChiTietPhieuNhap.MaSanPham
JOIN PhieuNhap ON ChiTietPhieuNhap.MaPhieu = PhieuNhap.MaPhieu WHERE MaNV = 'NV001';
--9.LầIy thồng tin các san phầm có giá trị nhập hàng từ 100,000 VNĐ đêIn 200,000 VNĐ
SELECT MaPhieu, SanPham.MaSanPham, TenSanPham, SoLuong, ThanhTien FROM SanPham
JOIN ChiTietPhieuNhap ON SanPham.MaSanPham = ChiTietPhieuNhap.MaSanPham
WHERE ThanhTien BETWEEN 100000 AND 200000;
--10.LầIy thồng tin các san phầm có sồI lượng tồEn kho lớn nhầIt SELECT * FROM SanPham
WHERE SoLuong = (SELECT MAX(SoLuong) FROM SanPham);
--11.LầIy thồng tin các san phầm có sồI lượng tồEn kho nho nhầIt SELECT * FROM SanPham
WHERE SoLuong = (SELECT MIN(SoLuong) FROM SanPham);
--12.LầIy thồng tin các san phầm có tên chứa từ 'Mì' hoặc 'Nước măIm'
SELECT * FROM SanPham WHERE TenSanPham LIKE N'%Mì%' OR TenSanPham LIKE N'%Nước măIm %';
--13.LầIy thồng tin các san phầm thuộc nhóm 'Sữa'
SELECT SanPham.MaNhom, TenSanPhaM, SoLuong FROM SanPham
JOIN NhomSanPham ON SanPham.MaNhom = NhomSanPham.MaNhom WHERE TenNhom LIKE N'Sữa';
--14.LầIy thồng tin san phầm có giá trị nhập hàng cao nhầIt
SELECT MaPhieu, SanPham.MaSanPham, TenSanPham, SoLuongNhap, ThanhTien FROM SanPham
JOIN ChiTietPhieuNhap ON SanPham.MaSanPham = ChiTietPhieuNhap.MaSanPham
WHERE ThanhTien = (SELECT MAX(ThanhTien) FROM ChiTietPhieuNhap);
--15.LầIy thồng tin san phầm có giá trị nhập hàng thầIp nhầIt
SELECT MaPhieu, SanPham.MaSanPham, TenSanPham, SoLuongNhap, ThanhTien FROM SanPham
JOIN ChiTietPhieuNhap ON SanPham.MaSanPham = ChiTietPhieuNhap.MaSanPham
WHERE ThanhTien = (SELECT MIN(ThanhTien) FROM ChiTietPhieuNhap);
--16.LầIy tên những san phầm được nhập vào từ nhà cung cầIp 'Vinamilk' hoặc 'Coca-Cola Việt Nam'
SELECT TenSanPham FROM SanPham
JOIN ChiTietPhieuNhap ON SanPham.MaSanPham = ChiTietPhieuNhap.MaSanPham
JOIN PhieuNhap ON ChiTietPhieuNhap.MaPhieu = PhieuNhap.MaPhieu
JOIN NhaCungCap ON PhieuNhap.MaCongTy = NhaCungCap.MaCongTy WHERE
TenCongTy IN ('Vinamilk', N'Coca-Cola Việt Nam');
--17.LầIy thồng tin các san phầm có sồI lượng tồEn kho giữa 10 và 50 SELECT * FROM SanPham
WHERE SoLuong BETWEEN 10 AND 50;
--18.LầIy thồng tin các san phầm có sồI lượng tồEn kho nho hơn 10 hoặc lớn hơn 50 SELECT * FROM SanPham
WHERE SoLuong < 10 OR SoLuong > 50;
--19.LầIy thồng tin các san phầm được nhập trong tháng 2/2024
SELECT SanPham.MaSanPham, TenSanPham, SoLuongNhap, ThoiGian, ThanhTien FROM SanPham
JOIN ChiTietPhieuNhap ON SanPham.MaSanPham = ChiTietPhieuNhap.MaSanPham
JOIN PhieuNhap ON ChiTietPhieuNhap.MaPhieu = PhieuNhap.MaPhieu WHERE
YEAR(ThoiGian) = 2024 AND MONTH(ThoiGian) = 2;
--20.LầIy thồng tin các san phầm được nhập từ 15-20/3/2024
SELECT SanPham.MaSanPham, TenSanPham, SoLuongNhap, ThoiGian, ThanhTien FROM SanPham
JOIN ChiTietPhieuNhap ON SanPham.MaSanPham = ChiTietPhieuNhap.MaSanPham
JOIN PhieuNhap ON ChiTietPhieuNhap.MaPhieu = PhieuNhap.MaPhieu
WHERE YEAR(ThoiGian) = 2024 AND MONTH(ThoiGian) = 3 AND DAY(ThoiGian) BETWEEN 15 AND 20; 3.2.3. Truy vấn nâng cao
--21.Tính tồng giá trị nhập hàng từng ngày
SELECT ThoiGian, SUM(ThanhTien) AS TongGiaTriNhap
FROM PhieuNhap JOIN ChiTietPhieuNhap ON PhieuNhap.MaPhieu = ChiTietPhieuNhap.MaPhieu GROUP BY ThoiGian;
--22.Tính đơn giá cua mồ7i san phầm trong từng phiêIu nhập
SELECT MaPhieu, SanPham.MaSanPham, (ThanhTien/SoLuongNhap) AS DonGia
FROM SanPham JOIN ChiTietPhieuNhap ON SanPham.MaSanPham = ChiTietPhieuNhap.MaSanPham;
--23.SăIp xêIp các nhà cung cầIp theo thứ tự giam dầEn sồI lượng phiêIu nhập
SELECT TenCongTy, COUNT(MaPhieu) SoPhieuNhap
FROM NhaCungCap JOIN PhieuNhap ON NhaCungCap.MaCongTy = PhieuNhap.MaCongTy GROUP BY TenCongTy ORDER BY SoPhieuNhap DESC;
--24.Tính trung bình tiêEn nhập hàng trên mồ7i phiêIu nhập trong tháng 2/2024
SELECT SUM(ThanhTien)/COUNT(DISTINCT PhieuNhap.MaPhieu) AS TrungBinh
FROM ChiTietPhieuNhap JOIN PhieuNhap ON ChiTietPhieuNhap.MaPhieu = PhieuNhap.MaPhieu
WHERE YEAR(ThoiGian) = 2024 AND MONTH(ThoiGian) = 2;
--25.Tính trung bình tiêEn nhập hàng trên mồ7i phiêIu nhập trong các ngày có nhập hàng
SELECT ThoiGian, SUM(ThanhTien)/COUNT(DISTINCT PhieuNhap.MaPhieu) AS TrungBinh
FROM ChiTietPhieuNhap JOIN PhieuNhap ON ChiTietPhieuNhap.MaPhieu = PhieuNhap.MaPhieu GROUP BY ThoiGian;
--26.LầIy thồng tin các san phầm có sồI lượng tồEn kho lớn hơn 50 và thuộc nhóm san phầm 'Sữa' SELECT * FROM SanPham
WHERE SoLuong > 50 AND MaNhom = (SELECT MaNhom FROM NhomSanPham WHERE TenNhom = N'Sữa');
--27.LầIy tên các nhà cung cầIp có sồI lượng phiêIu nhập lớn hơn 1 SELECT TenCongTy FROM (
SELECT TenCongTy, COUNT(MaPhieu) AS SoPhieuNhap
FROM NhaCungCap JOIN PhieuNhap ON NhaCungCap.MaCongTy = PhieuNhap.MaCongTy GROUP BY TenCongTy ) AS SubQuery WHERE SoPhieuNhap > 1;
--28.LầIy thồng tin nhần viên có nhiêEu phiêIu nhập nhầIt
SELECT MaNV, HoVaTen, DiaChi, SoPhieuNhap FROM (
SELECT NhanVien.MaNV, HoVaTen, DiaChi, COUNT(MaPhieu) AS SoPhieuNhap
FROM NhanVien JOIN PhieuNhap ON NhanVien.MaNV = PhieuNhap.MaNV GROUP BY
NhanVien.MaNV, HoVaTen, DiaChi ) AS SubQuery WHERE SoPhieuNhap = ( SELECT MAX(SoPhieuNhap) FROM (
SELECT MaNV, COUNT(MaPhieu) AS SoPhieuNhap FROM PhieuNhap GROUP BY MaNV ) AS SubQuery );
--29.LầIy tên san phầm có trong nhiêEu phiêIu nhập nhầIt SELECT TenSanPham FROM (
SELECT TenSanPham, COUNT(MaPhieu) AS SoPhieuNhap
FROM SanPham JOIN ChiTietPhieuNhap ON SanPham.MaSanPham = ChiTietPhieuNhap.MaSanPham GROUP BY TenSanPham ) AS SubQuery WHERE SoPhieuNhap = ( SELECT MAX(SoPhieuNhap) FROM (
SELECT MaSanPham, COUNT(MaPhieu) AS SoPhieuNhap FROM ChiTietPhieuNhap GROUP BY MaSanPham ) AS SubQuery );
--30.LầIy thồng tin các nhà cung cầIp có tồng giá trị nhập hàng lớn nhầIt
SELECT TenCongTy, TongTienNhapHang FROM (
SELECT TenCongTy, SUM(ThanhTien) AS TongTienNhapHang FROM NhaCungCap
JOIN PhieuNhap ON NhaCungCap.MaCongTy = PhieuNhap.MaCongTy
JOIN ChiTietPhieuNhap ON ChiTietPhieuNhap.MaPhieu = PhieuNhap.MaPhieu
GROUP BY TenCongTy ) AS SubQuery WHERE TongTienNhapHang = ( SELECT MAX(TongTienNhapHang) FROM (
SELECT TenCongTy, SUM(ThanhTien) AS TongTienNhapHang FROM NhaCungCap
JOIN PhieuNhap ON NhaCungCap.MaCongTy = PhieuNhap.MaCongTy
JOIN ChiTietPhieuNhap ON ChiTietPhieuNhap.MaPhieu = PhieuNhap.MaPhieu
GROUP BY TenCongTy ) AS SubQuery ); 3.2.4. Lập trình T-SQL
--31.LầIy thồng tin san phầm bằng cách sư dụng cursor DECLARE @MaNhom VARCHAR(20);
DECLARE @TenNhom NVARCHAR(100);
DECLARE @MaSanPham NVARCHAR(20);
DECLARE @TenSanPham NCHAR(25); DECLARE @SoLuong INT; DECLARE list_sp CURSOR FOR
SELECT MaSanPham, TenSanPham, SoLuong, MaNhom FROM SanPham OPEN list_sp
FETCH NEXT FROM list_sp INTO @MaSanPham, @TenSanPham, @SoLuong, @MaNhom WHILE @@FETCH_STATUS = 0 BEGIN
SELECT @TenNhom = TenNhom FROM NhomSanPham WHERE @MaNhom = MaNhom
PRINT @MaSanPham + ' ' + @TenSanPham + CAST(@SoLuong AS NCHAR(10)) + @TenNhom FETCH
NEXT FROM list_sp INTO @MaSanPham, @TenSanPham, @SoLuong, @MaNhom END CLOSE list_sp DEALLOCATE list_sp;
--32.Cập nhật sồI lượng san phầm trong mồ7i nhóm san phầm DECLARE @SoLuong INT DECLARE @MaNhom VARCHAR(10)
DECLARE Nhom CURSOR FOR SELECT MaNhom, COUNT(*) FROM SanPham GROUP BY MaNhom OPEN Nhom
FETCH NEXT FROM Nhom INTO @MaNhom, @SoLuong WHILE @@FETCH_STATUS = 0 BEGIN
UPDATE NhomSanPham SET SoLuong = @SoLuong WHERE @MaNhom = MaNhom
FETCH NEXT FROM Nhom INTO @MaNhom, @SoLuong END CLOSE Nhom DEALLOCATE Nhom;
--33.Thu tục lọc san phầm theo nhóm san phầm
CREATE PROCEDURE LocSanPham @MaNhom VARCHAR(20) AS BEGIN SELECT * FROM SanPham WHERE MaNhom = @MaNhom END;
--34.Tìm kiêIm san phầm theo tên
CREATE PROCEDURE TimKiemSanPham @search NVARCHAR(50) AS BEGIN SELECT * FROM SanPham
WHERE TenSanPham LIKE '%' + @search + '%'; END;
--35.Thu tục tìm kiêIm thồng tin nhần viên theo tên, hoặc theo mã nhần viên
CREATE PROCEDURE TimKiemNhanVien @search NVARCHAR(50) AS BEGIN SELECT * FROM NhanVien
WHERE MaNV LIKE @search OR HoVaTen LIKE '%' + @search + '%'; END;
--36.Thu tục tìm kiêIm thồng tin phiêIu nhập theo ngày
CREATE PROCEDURE TimKiemPhieuNhap_1 @Date DATE AS BEGIN
SELECT MaPhieu, HoVaTen, TenCongTy, ThoiGian FROM PhieuNhap
INNER JOIN NhanVien ON PhieuNhap.MaNV = NhanVien.MaNV
INNER JOIN NhaCungCap ON PhieuNhap.MaCongTy = NhaCungCap.MaCongTy WHERE ThoiGian = @Date; END;
--37.Thu tục tìm kiêIm thồng tin phiêIu nhập theo khoang thời gian
CREATE PROCEDURE TimKiemPhieuNhap_2 @From DATE, @To DATE AS BEGIN
SELECT MaPhieu, HoVaTen, TenCongTy, ThoiGian FROM PhieuNhap
INNER JOIN NhanVien ON PhieuNhap.MaNV = NhanVien.MaNV
INNER JOIN NhaCungCap ON PhieuNhap.MaCongTy = NhaCungCap.MaCongTy
WHERE ThoiGian BETWEEN @From AND @To END;
--38.Thu tục tìm kiêIm thồng tin chi tiêIt phiêIu nhập theo mã phiêIu nhập CREATE
PROCEDURE ChiTiet @MaPhieu VARCHAR(20) AS BEGIN
SELECT MaPhieu, TenSanPham, SoluongNhap, ThanhTien FROM ChiTietPhieuNhap
INNER JOIN SanPham ON ChiTietPhieuNhap.MaSanPham = SanPham.MaSanPham WHERE MaPhieu = @MaPhieu; END;
--39.Function đầEu vào mã phiêIu nhập, đầEu ra các chi tiêIt phiêIu nhập cua phiêIu nhập đó
CREATE FUNCTION ThongKeSanPham (@MaPhieu VARCHAR(20)) RETURNS TABLE AS RETURN (
SELECT TenSanPham, ThoiGian, SoLuongNhap, ThanhTien, TenCongTy, HoVaTen FROM ChiTietPhieuNhap
INNER JOIN SanPham ON ChiTietPhieuNhap.MaSanPham = SanPham.MaSanPham
INNER JOIN PhieuNhap ON ChiTietPhieuNhap.MaPhieu = PhieuNhap.MaPhieu
INNER JOIN NhaCungCap ON NhaCungCap.MaCongTy = PhieuNhap.MaCongTy
INNER JOIN NhanVien ON NhanVien.MaNV = PhieuNhap.MaNV
WHERE ChiTietPhieuNhap.MaPhieu = @MaPhieu );
--40.Function thồIng kê các phiêIu nhập trong 1 ngày hoặc các phiêIu nhập trong tháng
CREATE FUNCTION ThongKePhieuNhap (@Ngay DATE = NULL) RETURNS TABLE AS RETURN ( SELECT * FROM PhieuNhap
WHERE (@Ngay IS NOT NULL AND ThoiGian = @Ngay) OR (@Ngay IS NULL OR @Ngay = ''
AND YEAR(ThoiGian) = YEAR(GETDATE()) AND MONTH(ThoiGian) = MONTH(GETDATE())) );
--41.Function đầEu vào mã phiêIu nhập, đầEu ra là sồI tiêEn phai thanh toán cho phiêIu nhập đó