Đề tài : Xây dựng CSDL quản lý các hoạt động kinh doanh của
một siêu thò.
Phụ lục của giáo trình : Knowledge Discovery from Database
Giáo viên : Nguyễn Duy Nhất – [email protected]
Bài giải
Các yêu cầu tác nghiệp
(1.2) Lập danh sách hàng hóa được mua trong hóa đơn có mã là 1. Danh sách gồm
các thuộc tính : mã hàng, tên hàng, số lượng bán, giá bán.
SELECT CTHD.MaHang, HH.TenHang, CTHD.SoLuong, CTHD.DonGia
FROM (ChiTietHoaDon as CTHD inner join HoaDon as HD on
HD.MaHD=CTHD.MaHD) inner join HangHoa as HH on
CTHD.MaHang=HH.MaHang
WHERE HD.MaHD=1
(1.3) Lập danh sách mục hàng mà khách hàng có mã là 3242 đã mua trong năm
1998. Danh sách gồm các thuộc tính : mã hàng, tên hàng, tổng số lượng đã mua,
tổng giá trò.
SELECT CTHD.MaHang, HH.TenHang, Sum(CTHD.SoLuong) as TongSoLuong,
Sum(CTHD.SoLuong*CTHD.DonGia) as TongGiaTri
FROM (ChiTietHoaDon as CTHD inner join HoaDon as HD on
HD.MaHD=CTHD.MaHD) inner join HangHoa as HH on
CTHD.MaHang=HH.MaHang
WHERE (HD.MaKH=3242) and (Year(HD.NgayLap)=1998)
GROUP BY CTHD.MaHang, HH.TenHang
Thử nghiệm câu truy vấn với CSDL Walmart, kết quả trả về gồm 70 dòng có dạng
(1.4) Tính tổng doanh thu của doanh nghiệp (tức tổng số tiền thu về do bán hàng)
trong tháng 1 năm 1998.
SELECT Sum(HD.TongGiaTri) as DoanhThu
FROM HoaDon as HD

Tương tự (1.6), nhưng phải inner join thêm bảng LoaiHang để có cột tên loại.
SELECT LH.MaLoai, LH.TenLoai, Sum(CTHD.SoLuong) as TongSoLuong,
Sum(CTHD.SoLuong*CTHD.DonGia) as TongDoanhThu,
Sum((CTHD.DonGia - HH.DonGia)*CTHD.SoLuong) as LoiNhuan 2
FROM ((ChiTietHoaDon as CTHD inner join HoaDon as HD on
HD.MaHD=CTHD.MaHD) inner join HangHoa as HH on
CTHD.MaHang=HH.MaHang) inner join LoaiHang as LH on
HH.MaLoai=LH.MaLoai
WHERE Year(HD.NgayLap)=1998
GROUP BY LH.MaLoai, LH.TenLoai
Thử nghiệm câu truy vấn với CSDL Walmart, kết quả trả về có dạng
(1.8) Lập bảng thống kê tình hình tiêu thụ (doanh thu) của các mặt hàng theo từng
tháng trong năm 1998. Bảng gồm 15 cột, cột thứ nhất là mã hàng, cột thứ 2 là tên
hàng, 12 cột tiếp theo là doanh thu của từng mặt hàng trong từng tháng, cột thứ 15
là doanh thu trung bình.
Ta tư duy bài toán theo kiểu tập hợp. Bảng thống kê cần lập là một tập hợp nhiều
dòng dữ liệu, mỗi dòng biểu diễn tình hình tiêu thụ của một mặt hàng trong từng
tháng của năm 1998, nghóa là mỗi dòng là một bộ số liệu gồm 15 giá trò (ứng với 14
cột). Gọi q là tập hợp cần tìm, q được viết dưới dạng
(
)
{
}
12 12

⎫
,
trong đó 34
)(
i1
cqa,i=
, với mọi , và
i 1,2, ,12=
(
)
1
qx,y
là câu truy vấn (con) trả về doanh
thu của mặt hàng có mã là x trong tháng thứ y của năm 1998,
()
1
tb q x
′
=
, là doanh thu trung bình (theo tháng) của mặt hàng có mã là x trong
năm 1998.
(
1
qx,y


(SELECT Sum(CTHD.SoLuong*CTHD.DonGia)
FROM (ChiTietHoaDon as CTHD inner join HoaDon as HD on
HD.MaHD=CTHD.MaHD)
WHERE (CTHD.MaHang=HH.MaHang) and
(Year(HD.NgayLap)=1998) and (Month(HD.NgayLap)=12)
) as Thang12
FROM HangHoa as HH

Thử nghiệm câu truy vấn với CSDL Walmart, kết quả trả về có dạng
(1.9) Lập bảng thống kê doanh thu và lợi nhuận của doanh nghiệp trong 12 tháng
của năm 1998. Bảng gồm 3 cột : tháng, doanh thu, và lợi nhuận (do đó bảng sẽ có
12 dòng dữ liệu ứng với 12 tháng).
Bảng thống kê cần lập là một tập hợp q có dạng
(
)
{
}
qa,b,c=
,
trong đó q có 12 phần tử, tức 12 bộ số liệu có dạng
(
)
a,b,c
.
Nhận xét rằng a phải mang các giá trò từ 1 đến 12, nghóa là a thuộc tập hợp
{

)
1
qx

được viết bằng SQL như sau
SELECT Sum(HD.TongGiaTri)
FROM HoaDon as HD 5
WHERE (Year(HD.NgayLap)=1998) and (Month(HD.NgayLap)=x)
Gọi là lợi nhuận của doanh nghiệp trong tháng thứ x của năm 1998.
()
2
qx
(
)
2
qx

được viết bằng SQL như sau
SELECT Sum((CTHD.DonGia − HH.DonGia)*CTHD.SoLuong)
FROM (ChiTietHoaDon as CTHD inner join HoaDon as HD on
HD.MaHD=CTHD.MaHD) inner join HangHoa as HH on
CTHD.MaHang=HH.MaHang
WHERE Month(HD.NgayLap)=x and Year(HD.NgayLap)=1998
Khi đó, dạng SQL của q sẽ là
SELECT CT.Thang,

(SELECT Sum(HD.TongGiaTri)

,
trong đó a là một mã khách hàng, và b, c, d, e là thông tin về khách hàng có mã là
a. Nếu a được xác đònh trong bảng HoaDon thì bảng thống kê lập được sẽ bỏ qua
các khách hàng nào không có mua hàng trong năm 1998. Mặt khác, nếu a được xác
đònh trong bảng KhachHang, thì trong bảng thống kê lập được, các dòng ứng với
khách hàng nào không có mua hàng trong năm 1998 sẽ không có giá trò (Null), và
ta sẽ đặt giá trò ở đây bằng 0.
Trong bài giải sau đây, tác giả xác đònh a trong bảng KhachHang. Khi đó, q được
đònh nghóa bởi
()
()
()
()
()
1
2
a KhachHang.MaKH,
b KhachHang : MaKH a .TenKH,
q a,b,c,d,e c KhachHang:MaKH a .NgaySinh,
dqa,
eqa
⎧⎫
∈
⎪⎪
==
⎪⎪
⎪⎪
== =
⎨⎬
⎪⎪

WHERE (HD.MaKH=a) and (Year(HD.NgayLap)=1998)
Khi đó, dạng SQL của q sẽ là
SELECT KH.MaKH, (KH.HoKH + ' ' + KH.TenKH) as HoTen, KH.NgaySinh,

(SELECT Sum(HD.TongGiaTri)
FROM HoaDon as HD
WHERE (HD.MaKH=KH.MaKH) and (Year(HD.NgayLap)=1998)

) as KhoiLuongGiaoDich,

(SELECT Sum((CTHD.DonGia - HH.DonGia)*CTHD.SoLuong)
FROM (ChiTietHoaDon as CTHD inner join HoaDon as HD on
HD.MaHD=CTHD.MaHD
) inner join HangHoa as HH on
CTHD.MaHang=HH.MaHang

WHERE (HD.MaKH=KH.MaKH) and (Year(HD.NgayLap)=1998) 7
) as LoiNhuan
FROM KhachHang as KH

Để ý rằng trong bảng thống kê trả về từ câu truy vấn trên, giá trò tại các cột
KhoiLuongGiaoDich và LoiNhuan sẽ là NULL nếu như khách hàng này không có
mua hàng trong năm đang xét (1998).
Thử nghiệm với CSDL Walmart, kết quả có dạng

(1.2) Lập bảng thống kê về mức độ biến động doanh thu của các mặt hàng trong
năm 2008. Bảng gồm các cột : mã hàng, tên hàng, độ lệch chuẩn doanh thu. Sắp
xếp bảng giảm dần theo cột thứ 3.
Ở bài 1.8, ta đã lập được bảng thống kê doanh thu của các mặt hàng theo từng
tháng. Ta lập lại bảng này, nhưng thêm vào cột “Trung bình doanh thu” :

SELECT MaHang, TenHang, ((IsNull(Thang1,0) + IsNull(Thang2,0) +
IsNull(Thang3,0) + IsNull(Thang4,0) + IsNull(Thang5,0) +
IsNull(Thang6,0) + IsNull(Thang7,0) + IsNull(Thang8,0) +
IsNull(Thang9,0) + IsNull(Thang10,0) + IsNull(Thang11,0) +
IsNull(Thang12,0))/12) as TBDoanhThu
FROM (q)
trong đó, q là câu truy vấn ở bài 1.8.
Lại từ bảng này, ta lập bảng thống kê theo như đề bài yêu cầu, nghóa là thêm vào
cột “Độ lệch chuẩn doanh thu” :
SELECT MaHang, TenHang, TBDoanhThu,
((Abs(IsNull(Thang1,0) - TBDoanhThu) +
Abs(IsNull(Thang2,0) - TBDoanhThu) +
Abs(IsNull(Thang3,0) - TBDoanhThu) +
Abs(IsNull(Thang4,0) - TBDoanhThu) +
Abs(IsNull(Thang5,0) - TBDoanhThu) +
Abs(IsNull(Thang6,0) - TBDoanhThu) +
Abs(IsNull(Thang7,0) - TBDoanhThu) +
Abs
(IsNull(Thang8,0) - TBDoanhThu) +
Abs
(IsNull(Thang9,0) - TBDoanhThu) +
Abs(IsNull(Thang10,0) - TBDoanhThu) +
Abs(IsNull(Thang11,0) - TBDoanhThu) +


4 Có Có Có
… … … …

trong đó, mặt hàng x được gọi là có mua trong mã hóa đơn i khi trong bảng
ChiTietHoaDon, có 1 dòng t nào đó, mà t.MaHD = i và t.MaHang = x, và ở đây, ta
không quan tâm đến số lượng được mua hay đơn giá bán của x.
Để có được bảng thống kê này, ta cần lập bảng thống kê tương tự, có dạng

Mã hóa đơn Mặt hàng X Mặt hàng Y Mặt hàng Z
1
1
x

1
y

1
z

2
2
x

2
y

2
z

3

nguy cơ sai lầm của
nhận đònh được tính bằng số dòng có mua X, Y, và không mua Z, chia cho số dòng
có mua X, Y. Rõ ràng, độ tin cậy = 1 − nguy cơ sai lầm.
Bảng thống kê cần lập là một tập hợp có dạng
q10
(
)
{
}
qa,b,c,d=
,
trong đó a là một mã hóa đơn xác đònh trong HoaDon.MaHD, b là số lượng được
mua của mặt hàng X trong hóa đơn a (b=0 nếu hóa đơn a không có mua X), ý nghóa
tương tự lần lượt cho c và d, ứng với mặt hàng Y và Z.
q được đònh nghóa bởi

()
()
[]
()
[]
()
[]
aHoaDon.MaHD,
b ChiTietHoaDon : MaHD a,MaHang x .SoLuong b 0 ,
qa,b,c,d
c ChiTietHoaDon : MaHD a,MaHang y .SoLuong c 0 ,


IsNull((SELECT CTHD.SoLuong

FROM ChiTietHoaDon as CTHD

WHERE CTHD.MaHD=HD.MaHD and CTHD.MaHang=y),0) as Y,

IsNull((SELECT CTHD.SoLuong

FROM ChiTietHoaDon as CTHD

WHERE CTHD.MaHD=HD.MaHD and CTHD.MaHang=z),0) as Z
FROM HoaDon as HD

Dạng SQL tính độ tin cậy của nhận đònh
SELECT
(SELECT COUNT(q.MaHD)
FROM (…) as q
WHERE q.X>0 and q.Y>0 and q.Z>0
)*100.0/

(SELECT COUNT(q.MaHD)
FROM (…) as q
WHERE q.X>0 and q.Y>0
)
trong đó, dấu … đại diện cho câu truy vấn của q vừa xây dựng được ở trên. Việc phải
nhân tử số cho 100.0, thay vì 100, nhằm đổi kiểu dữ liệu của tử số từ kiểu nguyên
thành kiểu thực.

Thử nghiệm

=
⎪⎪
⎪⎪
=
⎩⎭
⎫
)
)
,
trong đó, là câu truy vấn trả về doanh thu của loại hàng x trong hóa đơn có
mã là a. Dạng SQL của là
(
1
qa,x
(
1
qa,x
SELECT Sum(CTHD.SoLuong*CTHD.DonGia)
FROM ChiTietHoaDon as CTHD inner join HangHoa as HH
on CTHD.Mahang = HH.MaHang
WHERE CTHD.MaHD=a
GROUP BY HH.MaLoai
HAVING HH.MaLoai=x

Dạng SQL của q là
SELECT HD.MaHD,

IsNull((SELECT Sum(CTHD.SoLuong*CTHD.DonGia)
FROM ChiTietHoaDon as CTHD inner join HangHoa as HH
on CTHD.Mahang = HH.MaHang

đề
SELECT lấy kết quả trả về của câu truy vấn tạo thành một bảng mới trong
CSDL, đặt tên là A.
Thử nghiệm với các loại hàng ngẫu nhiên x=61, y=99, và z=109 trên CSDL
Walmart, kết quả của câu truy vấn trên trả về bảng A có 17242 dòng, thời gian
thực hiện mất khoảng 5 phút trên máy tính Dual-core có tốc độ 1.7Ghz.
Chạy tiếp câu SQL tính độ tin cậy của nhận đònh :
Kết luận : Nhận đònh về thói quen tiêu dùng “Nếu khách hàng có mua loại hàng 61
và loại hàng 99 thì thường cũng sẽ mua loại hàng 109” có độ tin cậy là 12.85%,
nghóa là có nguy cơ sai lầm 87.15%. Điều này tương đương với nhận đònh “Nếu
khách hàng có mua loại hàng 61 và loại hàng 99 thì thường sẽ không mua loại 13
hàng 109” có độ tin cậy là 87.15%. Và sẽ một chút thú vò, khi ta biết rằng, loại
hàng 61, 99, và 109 có tên lần lượt là Fresh Vegetables, Fresh Fruit, Dried Fruit.
Nhận xét : Với 110 loại hàng có trong CSDL Walmart, và chi phí thời gian tính
toán không nhỏ khi đánh giá một nhận đònh, việc tìm ra được các nhận đònh có ý
nghóa và có độ tin cậy cao bằng cách chọn ngẫu nhiên không phải là điều dễ dàng,
nếu không muốn nói là không thể làm được (về mặt lý thuyết, với 110 loại hàng, ta
sẽ có nhận đònh kiểu như trên). Khó khăn này là mở đầu cho các giải
pháp heuristic thông minh để khắc phục, và tìm ra các nhận đònh tốt nhất, và cũng
là trọng tâm nghiên cứu của lónh vực Khám Phá Tri Thức Từ Cơ Sở Dữ Liệu, sẽ
được trình bày trong các chương còn lại của giáo trình này.