Tiểu luận môn học cơ sở dữ liệu: Quản lý mua bán

TIỂU LUẬN MÔN HỌC HỆ CƠ SỞ DỮ LIỆU ĐỀ TÀI: QUẢN LÝ MUA BÁN GVHD: Trần Thị Kim Chi Sinh viên thực hiện: Phạm Phú Nhân 09203631 Hoàng Anh Tuấn 09170111 Phạm Hải Băng 09219351 TRƯỜNG ĐẠI HỌC CÔNG NGHIỆP TP.HCM TP.HCM 11/2010 PHỤ LỤC I.GIỚI THIỆU ĐỀ TÀI 1. Mụch đích chọn đề tài 2. Yêu cầu đề tài II. XÂY DỰNG MÔ HÌNH THỰC THỂ KẾT HỢP Kiểu thực thể Các thuộc tính Các mối quan hệ Mô hình thực thể kết hợp III. MÔ HÌNH QUAN HỆ IV. CÁC RÀNG BUỘC V. ĐẠI SỐ QUAN HỆ B. NỘI DUNG I. GIỚI THIỆU ĐỀ TÀI 1. Mụch đích chọn đề tài Ngày nay, hoạt động trao đổi buôn bán không còn xa lạ với chúng ta, với tốc độ phát triển một cách mạnh mẽ các ngành nghề kéo theo buôn bán kinh doanh cũng phát triển theo. Cũng suất phát từ đó vấn đề quản lí dữ liệu đã cần được quan tâm phát triển. Ví dụ việc quản lý xuất nhập hàng, mua bán hàng hóa va quản lý tồn kho, những vấn đề này cần được thực hiện một cách chính xác, chặt chẽ. Nhưng để tổ chức quản lý những công việc trên bằng phương pháp thủ công ghi chép thì không phải là một công việc đơn giản, không phải ai cũng làm được mà cần phải có một kỹ năng nghiệp vụ và cần 1 hệ thống, 1 cơ sở dữ liệu quản lý chặt chẽ và chính xác để vịêc mua bán kinh doanh thụân tịên hơn, và nhất là không dẫn đến những thiệt hại cho hoạt động buôn bán. Vậy nên phương pháp thủ công này thì mất nhiều công sức và dễ bị nhầm lẫn.Vì lý do đó nhóm em chọn đề tài này để thực hành vịêc kinh doanh, quản lý vịêc mua bán, trau dồi kinh nghiệm và nhất là cách xây dựng 1 csdl bằng việc ứng dụng công nghệ thông tin. 2. yêu cầu đề tài Công ty ABC cần quản lý việc mua bán các mặt hàng cho các đại lý với mô tả như sau: Mỗi mặt hàng cần được lưu trữ các thông tin cơ bản sau: mã hàng (không trùng nhau), tên hàng, đơn vị tính, mô tả, tên nhà sản xuất, đơn giá, số lượng tồn kho. Các đại lý bán/mua hàng cũng cần được lưu lại các thông tin như: Mã số đại lý (không trùng nhau), tên đại lý, địa chỉ của đại lý, số điện thoại, số Fax, địa chỉ Email, tên người đại diện. Dữ liệu bán/mua cần được lưu trữ riêng biệt. Mỗi lần bán/mua hàng: công ty cần lưu lại thông tin từ các hoá đơn bán/mua bao gồm các thông tin: số hoá đơn, ngày bán/mua, họ tên và địa chỉ của người mua/ người bán (đại lý), tổng số tiền mua/bán, số tiền thuế VAT, tổng số tiền sau khi đã tính thuế, người lập phiếu (người phụ trách việc mua/bán). Bên cạnh các thông tin trên, công ty cũng cần lưu lại thông tin chi tiết của từng hoá đơn như: số hoá đơn, mã hàng đươc bán/mua, số lượng bán/mua, đơn giá của từng mặt hàng. Yêu cầu được đề ra là xây dựng cơ sở dư cho cty qản lý vịêc mua bán với các đại lý sỉ, lẻ. dễ dàng, thụân tiện cho việc qản lý, giảm chi phí do tiền chi cho nhân công, tránh các thiệt hại không đáng có do sai sót của người quản lý. II .XÂY DỰNG MÔ HÌNH THỰC THỂ KẾT HỢP Kiểu thực thể KHACHHANG MATHANG HDNHAP HDXUAT Các thuộc tính *KHACHHANG Makh( khóa chính ) Tenkh Diachi Sdt Fax Email Daidien *MATHANG Mamh (khóa chính) tenmh dvt mota nsx dongia slton *HDNHAP sohdnhap(khóa chính) ngaynhap makh(khóa ngoại) *HDXUAT sohdxuat(khóa chính) makh (khóa ngoại) ngayxuat Các mối quan hệ Giữa khách hàng (đại lý) và hóa đơn nhập (hóa đơn mua hàng của cửa hàng) KHACHANG HDNHAP Sohdnhap Ngaynhap makh 1 23/1/2010 1 2 14/2/2010 1 3 14/2/2010 2 4 23/1/2010 3 Một khách hàng hay một đại lý thì có thể bán cho cửa hàng của chúng ta một hoặc nhiều lần cần thiết và sẽ có một hoặc nhiều hóa đơn , tuy nhiên một hóa đơn nhập hàng của cửa hàng chúng ta thì lại là của một và chỉ một đại lý duy nhất. Giữa khách hàng (đại lý) và hóa đơn xuất (hd bán hàng của cửa hàng) HDXUAT KHACHANG Sohdxuat Ngaynhap makh 1 21/1/2010 1 2 19/2/2010 1 3 17/2/2010 2 4 26/1/2010 3 Một khách hàng hay một đại lý thì có thể mua một hoặc nhiều mặt hàng từ cửa hàng của chúng ta và sẽ có một hoặc nhiều hóa đơn , tuy nhiên một hóa đơn bán hàng của cửa hàng chúng ta thì lại là của một và chỉ một đại lý duy nhất. Giữa mặt hàng và hóa đơn nhập HDNHAP MATHANG Trong một hóa đơn nhập hàng (hd mua hàng của đại lý ) thì có thể có nhiều mặt hàng trong đó và ngược lại một mặt hàng thì cũng có thể có mặt trong nhiều hóa đơn nhập khác nhau vì vậy xuất hiện mối quan hệ nhiều nhiều yêu cầu ta phải đưa về quan hệ một nhiều bằng cách thêm một quan hề khác đó là chi tiết hóa đơn nhập (CTNHAP) gồm các thuộc tính sau: *CTNHAP sohdnhap (khóa chính) mamh (khóa chính) soluong dg vat tt Giữa mặt hàng và hóa đơn xuất HDXUAT MATHANG Trong một hóa đơn xuất hàng (hd bán hàng của đại lý ) thì có thể có nhiều mặt hàng trong đó và ngược lại một mặt hàng thì cũng có thể có mặt trong nhiều hóa đơn xuất khác nhau vì vậy xuất hiện mối quan hệ nhiều nhiều yêu cầu ta phải đưa về quan hệ một nhiều bằng cách thêm một quan hề khác đó là chi tiết hóa đơn xuất (CTXUAT) gồm các thuộc tính sau: *CTNHAP sohdxuat (khóa chính) mamh (khóa chính) soluong dg vat tt Xây dựng mô hình thực thể kết hợp III. MÔ HÌNH QUAN HỆ KHACHHANG (makh,tenkh,diachi,daidien) MATHANG (mamh, tenmh, dvt, mota, nsx, dongia, slton) HDNHAP (sohdnhap,ngaynhap,makh,tongtien) HDXUAT (sohdxuat, ngayxuat, makh,tongtien) SDT (makh, sdt) FAX (makh, fax) EMAIL (makh, email) CTNHAP (sohdnhap, mamh, soluong, dg, vat, tt) CTXUAT (sohdxuat, mamh, soluong, dg, vat, tt) IV. CÁC RÀNG BUỘC KHACHHANG Với r là một quan hệ của khách hàng ta có các ràng buộc toàn vẹn sau: R1: " t1, t2 € r t1.MAKH ≠ t2.MAKH R1 Thêm Xóa Sửa R + - + R2: " t € r t.tenkh is not null R2 Thêm Xóa Sửa R + - + MATHANG Với r là một quan hệ của mặt hàng ta có các ràng buộc toàn vẹn sau: R1: " t1, t2 € r t1.MAMH ≠ t2.MAMH R1 Thêm Xóa Sửa R + - + R2: " t € r t.tenmh is not null R2 Thêm Xóa Sửa R + - + R3: " t € r t.dongia is not null R3 Thêm Xóa Sửa R + - + R4: " t € r t.dvt is not null R4 Thêm Xóa Sửa R + - + R5: " t € r t.nsx is not null R5 Thêm Xóa Sửa R + - + HDNHAP Với r là một quan hệ của hóa đơn nhập ta có các ràng buộc toàn vẹn sau: R1: " t1, t2 € r t1.sohdnhap ≠ t2.sohdnhap R1 Thêm Xóa Sửa R + - + R2: " t € r t.ngaynhap is not null R2 Thêm Xóa Sửa R + - + R3 : " t € r t.tongtien is not null R3 Thêm Xóa Sửa R + - + Với r, s lần lượt là một quan hệ của HDNHAP, KHACHHANG ta có ràng buộc toàn vẹn sau R r [makh] Í s[makh] R3 Thêm Xóa Sửa R + - + S - + - HDXUAT Với r là một quan hệ của hóa đơn xuất ta có các ràng buộc toàn vẹn sau: R1: " t1, t2 € r t1.sohdxuat ≠ t2.sohdxuat R1 Thêm Xóa Sửa R + - + R2: " t € r t.ngayxuat is not null R2 Thêm Xóa Sửa R + - + R3 : " t € r t.tongtien is not null R3 Thêm Xóa Sửa R + - + Với r, s lần lượt là một quan hệ của HDXUAT, KHACHHANG ta có ràng buộc toàn vẹn sau R r [makh] Í s[makh] R3 Thêm Xóa Sửa R + - + S - + - CTNHAP Với r là một quan hệ của CTNHAP ta có các ràng buộc toàn vẹn sau: R1: " t1, t2 € r t1. sohdnhap, mamh ≠ t2. sohdnhap, mamh R1 Thêm Xóa Sửa R + - + R2: " t € r t.soluong is not null R2 Thêm Xóa Sửa R + - + R3: " t € r t.dg is not null R3 Thêm Xóa Sửa R + - + R4: " t € r t.VAT is not null R4 Thêm Xóa Sửa R + - + R5: " t € r t.tt is not null R5 Thêm Xóa Sửa R + - + CTXUAT Với r là một quan hệ của CTXUAT ta có các ràng buộc toàn vẹn sau: R1: " t1, t2 € r t1. sohdxuat, mamh ≠ t2. sohdxuat, mamh R1 Thêm Xóa Sửa R + - + R2: " t € r t.soluong is not null R2 Thêm Xóa Sửa R + - + R3: " t € r t.dg is not null R3 Thêm Xóa Sửa R + - + R4: " t € r t.VAT is not null R4 Thêm Xóa Sửa R + - + R5: " t € r t.tt is not null R5 Thêm Xóa Sửa R + - + EMAIL Với r là một quan hệ của EMAIL ta có các ràng buộc toàn vẹn sau: R1: " t1, t2 € r t1. makh ≠ t2. makh R1 Thêm Xóa Sửa R + - + FAX Với r là một quan hệ của FAX ta có các ràng buộc toàn vẹn sau: R1: " t1, t2 € r t1. makh ≠ t2. makh R1 Thêm Xóa Sửa R + - + Với r là một quan hệ của EMAIL ta có các ràng buộc toàn vẹn sau: R1: " t1, t2 € r t1. makh ≠ t2. makh R1 Thêm Xóa Sửa R + - + V. ĐẠI SỐ QUAN HỆ Liệt kê tên các kh mua loại hàng có mã F5 Ptenkh(KHACHHANG mamh=mamh smamh=‘F5’(MATHANG)) liệt kê các mặt hàng có đơn giá =5tr sdongia=5tr (MATHANG) Liệt kê các công ty nhập & xuất có số lượng bằng nhau HDNHAP HDXUAT soluong=soluong Lịêt kê các mặt hàng có số lượng tồn >=50 sslton=50 v slton>50 (MAT HANG) Liệt kê những khách hàng mua mặt hàng là ‘ca’ smamh=ca CTXUAT HDXUAT Sohdxuat=Sohdxuat VI. TRUY VẤN SQL --tạo cơ sở dữ liệu bán hàng CREATE DATABASE banhang ON PRIMARY ( NAME=BH1_data, filename='D:\tieuluan\BH1.mdf', SIZE=10,MAXSIZE=50,FILEGROWTH=15% ), ( NAME=BH2_data, filename='D:\tieuluan\BH2.ndf', SIZE=10,MAXSIZE=50,FILEGROWTH=15%), FILEGROUP BH1group ( NAME=BH3_data, FILENAME='D:\tieuluan\BH3.ndf', SIZE=10,MAXSIZE=50,FILEGROWTH=5%) LOG ON ( NAME=BH_log, FILENAME='D:\tieuluan\BH.ldf', SIZE=5, MAXSIZE=25, FILEGROWTH=5%) drop table mathang use banhang drop table ctxuat --tạo bảng khách hàng create table KHACHHANG ( MAKH char(5)not null PRIMARY KEY, TENKH Varchar(30) NOT NULL, DIACHI Varchar(40), DAIDIEN VARCHAR(30) ) --tạo bảng mặt hàng create table MATHANG ( mamh char(5) not null PRIMARY KEY, tenmh Varchar(20)unique not null, dvt char(10)not null, mota VARCHAR(20), nsx DateTime NOT NULL, dongia smallmoney not null, slton int ) --tạo bảng chi tiết nhập hàng hóa create table ctnhap ( sohdnhap int identity not null, mamh char(5) not null, soluong int not null, dg smallmoney, vat float , tt as((soluong*dg)+(soluong*dg*vat)/100), primary key (sohdnhap,mamh) ) --tạo bảng chi tiết xuất hàng hóa create table ctxuat ( sohdxuat int identity not null, mamh char(5) not null, soluong int not null,dg smallmoney, vat float , tt as((soluong*dg)+(soluong*dg*vat)/100), primary key (sohdxuat,mamh) ) --tạo bảng hóa đơn nhập create table hdnhap ( sohdnhap int identity not null primary key, makh char(5) foreign key references khachhang(makh), ngaynhap DateTime NOT NULL DEFAULT (GetDate()), tongtien float ) --tạo bảng hóa đơn xuất create table hdxuat ( sohdxuat int identity not null primary key, makh char(5) foreign key references khachhang(makh), ngayxuat DateTime NOT NULL DEFAULT (GetDate()), tongtien float ) --tạo bảng số điện thoại của khách hàng create table sdt ( makh char(5) primary key, sodt char(12) ) --tạo bảng địa chỉ email của khách hàng create table email ( makh char(5) primary key, email char(40) ) --tạo bảng số fax của khách hàng create table fax ( makh char(5) primary key, fax char(20) ) --các câu lệnh truy vấn --Cho biết hai mặt hàng có cùng đơn giá SELECT mh1.tenmh, mh2.tenmh,mh1.dongia FROM mathang mh1, mathang mh2 WHERE mh1.dongia = mh2.dongia --Danh sách các hoá đơn gồm Mahd, tenkh SELECT h.makh, tenkh FROM hdnhap h, khachhang k WHERE h.makh = k.makh --Cho biết tổng tiền xuất của từng khách hàng SELECT makh, SUM ((soluong*dg)+(soluong*dg*vat)/100) AS Total FROM ctxuat c,hdxuat h where c.sohdxuat=h.sohdxuat GROUP BY makh --Cho biết tổng tiền của từng hóa đơn SELECT sohdnhap, SUM((soluong*dg)+(soluong*dg*vat)/100) AS Total FROM ctnhap GROUP BY sohdnhap --Cho biết trung bình thu được theo từng hóa đơn SELECT sohdnhap, AVG ((soluong*dg)+(soluong*dg*vat)/100) AS AVGTOTAL FROM ctnhap GROUP BY sohdnhap --Cho biết các sản phẩm có đơn giá cao nhất SELECT * FROM mathang WHERE dongia>= ALL (SELECT dongia FROM mathang) --Cho biết các sản phẩm có đơn vị tính có chữ cai và có đơn giá cao nhất SELECT * FROM mathang WHERE dvt like '%cai%' and dongia >= ALL (SELECT dongia FROM mathang) --liệt kê những khách hàng cùng địa chỉ select distinct a.makh,a.tenkh,a.diachi from khachhang a,khachhang b where a.makh!=b.makh and a.diachi=b.diachi; --liệt kê nhữn khách hàng mua nhiều hơn 2 sản phẩm select a.makh, a.tenkh,sosp=count(c.mamh) from ctxuat c,khachhang a,hdxuat b where a.makh=b.makh and b.Sohdxuat=c.Sohdxuat group by a.makh, a.tenkh having count(c.mamh)>=2; --liệt kê những mặt hàng còn tồn trong kho select * from mathang where slton>0 --liệt kê mặt hàng tên là cam select * from mathang where tenmh like 'cam'