SQL Server - Quản lý cửa hàng đĩa

Bo co bi tp ln
MC LC
M U.....................................................................................................................................................3
CHNG I: KHO ST H THNG......................................................................................................5
I. M T H THNG............................................................................................................................5
1) Tng quan.............................................................................................................................................................5
2) Quy trnh x l......................................................................................................................................................5
II. YU CU TIN HC HA.................................................................................................................8

1) Ni dung ti......................................................................................................................................................8
2) M t yu cu........................................................................................................................................................8
III. GII QUYT VN .....................................................................................................................9

CHNG II: M HNH THC TH LIN KT E-R
V M HNH QUAN H........................................................................................................................10
I. CC BC XY DNG...................................................................................................................10
II. K HIU S DNG.........................................................................................................................10
III. P DNG BI TON.....................................................................................................................10
CHNG III: C S D LIU SQL SERVER 2005.............................................................................15
I. M T CC BNG V QUAN H.................................................................................................15
1) M t cc bng trong c s d liu.....................................................................................................................15
2) Cc bng ca c s d liu.................................................................................................................................15
3) S m t mi quan h gia cc bng.............................................................................................................18
II. CC TH TC M T YU CU CA C S D LIU........................................................18
1) Cc th tc m t chc nng c bn...................................................................................................................18
2) Cc th tc m t chc nng c yu cu......................................................................................18

3) Chi tit cc Store Procedure thc thi yu cu.....................................................................................................19
PH LC 1: K HOCH TNG TH....................................................................................................24

I) GII THIU.......................................................................................................................................24
1. Tn ti: Qun l ca hng a (Software + Games).......................................................................................24
2. Thng tin v nhm xy dng ti.....................................................................................................................24
3. Mc ch ca ti liu: .........................................................................................................................................24
4. Phm vi ti liu....................................................................................................................................................24
5. M t ti liu.......................................................................................................................................................24
II) Ni dung.............................................................................................................................................27
Qun l ca hng a
Trang 1
Bo co bi tp ln
PH LC 2: BIN BN KHO ST......................................................................................................28
I) Thnh phn kho st:.........................................................................................................................28
II) Ni dung kho st:............................................................................................................................28
1. Thng tin chung:.................................................................................................................................................28
2. Tin hnh kho st:.............................................................................................................................................28
II) Mc tiu ca ca hng:.....................................................................................................................31

III) Kt lun ca nhm kho st............................................................................................................32
PH LC 3: NH GI THNH VIN................................................................................................33
Qun l ca hng a
Trang 2
Bo co bi tp ln
M U
Ngy nay x hi pht trin khng ngng nhanh chng, x hi cng pht trin th thng tin
cng to ln di do, phong ph v a dng. Thng tin thc s tr thnh ngun ti nguyn v
cng qu gi trong i sng khoa hc k thut, kinh doanh cng nh mi hot ng khc ca x
hi. Nhng ng dng tin hc tr gip con ngi sn xut hiu qu thc y cc tin trnh lm
vic c nhanh chng. Khi lng thng tin gia tng th nhu cu ca vic qun l, t chc v x
l thng tin c mt ngha quan trng i vi nhng ngi lnh o. Trc nhng nhu cu nh
vy th vic lu tr v x l thng tin bng phng php th cng v s tay truyn thng khng
cn ph hp na. Chnh v vy vn quan trng c t ra l lm th no khai thc nhanh,
chnh xc v hiu qu lng thng tin qu gi ch c my tnh l cng c in t cho php x
l nhanh chng cc nhu cu khc nhau, sn phm ca n c ng dng rng ri trong thc tin.
Tin hc thc s tr l mt ngnh khoa hc v cc qu trnh thu thp x l v lu tr thng tin
mt cch c hiu qu nht, thc c vai tr v ngha quan trng nn trong ln lm bo
co bi tp ln mn n C s d liu ny nhm xin c ng dng tin hc vo cng tc qun
l a ca ca hng bn a Heaven. Thc hin hnh thc qun l bn a mt cch khoa hc v
hiu qu, tit kim chi ph cho ca hng cng nh em li s thoi mi nht cho khch hng khi
mua hng.
1. Mc ch, ngha ca bo co:
Nghin cu, phn tch h thng qun l a nhm xy dng C s d liu qun l xut nhp
a cho cc ca hng va v nh, p dng cho Ca hng a Heaven.
Xy dng C s d liu cho chng trnh qun l bn a, qua rt ra cc kinh nghim thc
tin xy dng cc chng trnh qun l bn hng ni chung v qun l ca hng a ni ring
v mang ngha thc tin.
2. i tng v phm vi nghin cu:
i tng nghin cu gm:
Cc loi a, hiu phng thc phn loi a.
Cc ho n nhp xut.
Phm vi nghin cu: trong mt ca hng bn a c th.

3. Kt cu v ni dung ca bo co
Qun l ca hng a
Trang 3
Bo co bi tp ln
-
Tn bo co.
Bo co gm:
M u
Chng I: Kho st h thng.
Chng II: M hnh thc th lin kt E-R v M hnh quan h.
Chng III: C s d liu SQL Server 2005.
Kt lun.
Ph lc.
4. Nhng thut ng v cm t vit tt

STT
Cm t vit tt
Thut ng
Vit y
ngha
CSDL
C s d liu
E-R
Entity Relationship
M hnh thc th lin kt
PK
Primary Key
Kho chnh
FK
Foreign Key
Kho ngoi
Qun l ca hng a
Trang 4
Bo co bi tp ln
CHNG I: KHO ST H THNG

I. M T H THNG
1) Tng quan
Ca hng s phn chia a theo th loi v nh m s tu theo th loi c phn chia.
Mi a s c cc thng tin chnh: M a, Tn a, Th loi, H my.
V cng vic nhp xut hng, ca hng s nhp a theo tng t v a ra cc ho n nhp
hng c th. i vi bn hng c chia thnh 2 phng thc nhng vn cng mt loi ho n
xut hng nh nhau. Trong cc ho n s c cc thng tin c bn v khch hng v thng tin
a s bn.
Ch ca hng s c th thng k mt cch c th doanh thu, s lng, bt k lc no theo
mong mun v theo di thng tin bn hng ca nhn vin.
2) Quy trnh x l
2.1. Phn loi a
-
a tr chi dnh cho cc th loi my khc nhau c nh m theo th loi bao gm:
a PC: dnh ci t cho cc my vi tnh c nhn.
a PS2: dnh cho dng my PlayStation 2 ca Sony.
a Xbox: dnh cho dng my Xbox 360, Xbox Live ca Microsoft.
a Wii: dnh cho dng my Wii ca Nintendo.

-
a phn mm gm cc phin bn h iu hnh v cc phn mm thng dng
c nh m S+Tn th loi bao gm:
a h iu hnh: gm cc phin bn h iu hnh Windows t Windows 98 n

Windows 7 v cc phin bn Linux t ph thng: Fedora, Ubuntu,
a phn mm tin ch: gm cc a phn mm tin ch khi vn phng nh b

Microsoft Office, HTKK Thu, v cc phn mm chuyn dng phc v khi thit
k nh Adobe Photoshop, AutoCAD, CorelDRAW,
Ngoi ra gm cc a phn mm ph thng khc.
Qun l ca hng a
Trang 5
Bo co bi tp ln
2.2. Phng thc trin khai ho n
-
Coi i tng khch hng va l ngun nhp hng, va l khch mua hng.
Ho n c trin khai lm 2 loi chnh: Nhp hng v Xut hng.
2.3. Mc tiu ca c s d liu

-
y cc chc nng c bn nhp xut d liu cho tng bng.
Thc hin thm cc yu cu qun l nh: thng k, bo co,... theo yu cu ca cng vic
kinh doanh.
2.4. Mt s mu biu
Qua qu trnh kho st trn, ti ca hng a Heaven c cc mu biu sau:
-
n t hng:
Ca hng a Heaven
a ch: 668 ng Lng - ng a - H Ni
in thoi: (04)3xxxxxxx - Di ng: 098xxxxxxx
N T HNG
H tn: ...
a ch: ..
in thoi:
Hnh thc thanh ton:
STT
M a
Tn a
S lng
n gi
Thnh tin
Cng tin hng: ..

Tng cng thanh ton: ..
S tin vit bng ch: ...
Ngi mua hng
Ngi bn hng
(K, ghi r h tn)
(K, ghi r h tn)
Qun l ca hng a
Trang 6
Bo co bi tp ln
-
Ho n nhp hng:
n v bn hng:
a ch: ..
S ti khon: .
in thoi:
HO N NHP HNG
n v mua hng: Ca hng a Heaven
S ti khon: 1234-5678-ABCD
Hnh thc thanh ton:
STT
M a
Tn a
S lng
n gi
Thnh tin
Cng tin hng: ..

Ngi giao hng
Ngi nhn hng
(K, ghi r h tn)
(K, ghi r h tn)
Ho n bn hng:
Ca hng a Heaven
HO N BN HNG
H tn: ...
a ch: ..
in thoi:
Hnh thc thanh ton:
STT
M a
Tn a
S lng
n gi
Thnh tin
Cng tin hng: ..

Ngi mua hng
Ngi bn hng
(K, ghi r h tn)
(K, ghi r h tn)
Qun l ca hng a
Trang 7
Bo co bi tp ln
II. YU CU TIN HC HA
1) Ni dung ti
Vi nhu cu thng tin v quy m kinh doanh ngy cng m rng ca ca hng, to iu kin
thun li cho vic qun l a cng nh vic nhp xut a v truy xut thng tin nhanh chng v
a. Do CSDL i hi phi lp trnh thc hin c y cc thao tc nhp xut, thng
k thng tin mt cch y v c th, gip cho cp nht nhng a mi cho ca hng nhanh
chng, d hng hn.
Nhn vin phi nhp cc thng tin cho cc a mi, nh cung cp mi, khch hng mi vo
trong d liu v c s kim tra v tnh chnh xc, ng n ca d liu.
Nhn vin c php chnh sa, xa nhng thng tin sai, khng ph hp theo quyn hn cho
php.
Ch ca hng theo di qu trnh mua bn, thng k doanh thu ca ca hng.
2) M t yu cu
Yu cu chc nng:
Lit k thng tin a:

o Ton b
o Theo thi gian
o Theo nhn vin
o Theo khch hng
Lit k nhn vin theo doanh thu.
Thng k tng doanh thu:

o Ton b
o Theo thng
o Theo nm
Bo co chung v tng nhp, xut:

o Ton b
Qun l ca hng a
Trang 8
Bo co bi tp ln
o Theo thng
o Theo nm
Lit k cc a cha bn.
Lit k cc a bn chy nht.
III. GII QUYT VN

Tng bc i vo phn tch, thit k h thng gii quyt vn .
Kho st: Tin hnh kho st mt khu vc, a ch c th ly thng tin thc t.
M hnh ha nghip v: Da trn kho st thc trng, m hnh ha cc nghip v bng

m hnh thc th lin kt, t chuyn sang m hnh quan h.
Thit k: T kt qu cc m hnh quan h, trin khai thit k CSDL t c cc

kt qu nh mong mun.
Qun l ca hng a
Trang 9
Bo co bi tp ln
CHNG II: M HNH THC TH LIN KT E-R

V M HNH QUAN H
I. CC BC XY DNG
-
Bc 1: Xc nh cc thc th.
Bc 2: c t m hnh.
Bc 3: M hnh thc th lin kt.
Bc 4: M hnh quan h.
II. K HIU S DNG

-
Thuc tnh:
Tp thc th:
Quan h:
(min,max): Rng buc lin kt.
III. P DNG BI TON

-
Bc 1: Xc nh cc thc th
o
Khch hng.
Nhn vin bn hng.
Ho n.
Loi phiu xut nhp.
a.
o
Cc tp thc th:
Thc th yu:
Th loi a.
Chi tit ho n.
Bc 2: c t m hnh
Qun l ca hng a
Trang 10
Bo co bi tp ln
KhachHang (Khch hng):
Thuc tnh
Vit y
nh dng
c t
PK
MaKH
M khch hng
Text
TenKH
Tn khch hng
Text
a ch khch hng
Text
SDT
S in thoi khch hng
Text
Email
Th in t khch hng
Text
Dchi_KH
NhanVienBH (Nhn vin bn hng):

Thuc tnh
Vit y
nh dng
c t
MaNV
M ngi bn
Text
PK
TenNV
Tn ngi bn
Text
Dchi_NV
a ch ngi bn
Text
TheLoaiDia (Th loi a):

Thuc tnh
Vit y
nh dng
c t
MaTL
M th loi a
Text
PK
TenTL
Tn th loi a
Text
Qun l ca hng a
Trang 11
Bo co bi tp ln
Dia (a):
Thuc tnh
Vit y
nh dng
c t
MaDia
M a
Text
PK
TenDia
Tn a
Text
Thuc tnh
Vit y
nh dng
c t
MaHD
M ho n
Text
PK
NgayBan
Ngy bn
Date/Time
HoaDon (Ho n):
CTiet_HD (Chi tit ho n):

Thuc tnh
Vit y
nh dng
SoLuong
S lng bn
Number
DonGia
Gi tin
Number
c t
Qun l ca hng a
Trang 12
Bo co bi tp ln
-
Bc 3: M hnh thc th lin kt
Hnh 2.1: M hnh thc th lin kt ca ca hng bn a
Bc 4: M hnh quan h
Qun l ca hng a
Trang 13
Bo co bi tp ln
+ KhachHang (MaKH, TenKH, Dchi_KH, SDT, Email)
+ NhanVienBH (MaNV, TenNV, Dchi_NV)
+ Dia (MaDia, TenDia, MaTL)
+ TheLoaiDia (MaTL, TenTL)
+ HoaDon (MaHD, NgayBan, MaNV, MaKH, MaLoai)
+ CTiet_HD (MaHD, MaDia, SoLuong, DonGia)
+ LoaiPhieu (MaLoai, TenLoai)
Qun l ca hng a
Trang 14
Bo co bi tp ln
CHNG III: C S D LIU SQL SERVER 2005

I. M T CC BNG V QUAN H
1) M t cc bng trong c s d liu
STT
Tn bng
Tn nh ngha
ngha
NhanVienBH
Nhn vin bn hng
Cc thng tin v nhn vin
KhachHang
Khch hng
Cc thng tin v khch hng
HoaDon
Ho n
Thng tin chung v giao dch
CTiet_HD
Chi tit ho n
Thng tin chi tit v tng a c giao dch
LoaiPhieu
Loi phiu xut nhp
Cho bit loi ho n l nhp hay bn hng
Dia
Cho bit thng tin chung v a
TheLoaiDia
Th loi a
Cho bit a thuc th loi g
2) Cc bng ca c s d liu
-
Bng HoaDon
Tn ct (Trng)
Kiu d liu
Cho php trng
M t
MaHD
nchar(10)
NgayBan
datetime
MaNV
char(3)
FK
MaKH
char(5)
FK
MaLoai
nchar(10)
FK
PK
Bng KhachHang
Tn ct (Trng)
Kiu d liu
MaKH
char(5)
TenKH
nvarchar(50)
Dchi_KH
nvarchar(100)
Cho php trng
M t
PK
Qun l ca hng a
Trang 15
Bo co bi tp ln
SDT
int
Email
nchar(20)
Tn ct (Trng)
Kiu d liu
Cho php trng
MaNV
char(3)
TenNV
nvarchar(50)
Dchi_NV
nvarchar(100)
Bng NhanVienBH
M t
PK
Bng LoaiPhieu
Tn ct (Trng)
Kiu d liu
MaLoai
nchar(10)
TenLoai
nvarchar(50)
Cho php trng
M t
PK
Qun l ca hng a
Trang 16
Bo co bi tp ln
-
Bng CTiet_HoaDon
Tn ct (Trng)
Kiu d liu
MaHD
nchar(10)
MaDia
nchar(10)
SoLuong
bigint
DonGia
float
Cho php trng
M t
PK
Bng Dia
Tn ct (Trng)
Kiu d liu
MaDia
nchar(10)
TenDia
nvarchar(50)
MaTL
nchar(10)
Cho php trng
M t
PK
FK
Bng TheLoaiDia
Tn ct (Trng)
Kiu d liu
MaTL
nchar(10)
TenTL
nvarchar(50)
Cho php trng
M t
PK
Qun l ca hng a
Trang 17
Bo co bi tp ln
3) S m t mi quan h gia cc bng
Hnh 3.1: Mi quan h gia cc bng trong CSDL
II. CC TH TC M T YU CU CA C S D LIU
1) Cc th tc m t chc nng c bn
Tn th tc
Chc nng
Ghi ch
dbo.sp_Insert[Tn bng]
Nhp d liu cho cc bng
dbo.sp_Delete[Tn bng]
Xo d liu tng bng
Cho php sa, xo, thng k
dbo.sp_Update[Tn bng]
Sa d liu tng bng
y theo cc kho, cc
dbo.sp_Select[Tn bng]
Thng k d liu tng bng
trng
2) Cc th tc m t chc nng c yu cu
STT
Lit k a
Tn th tc
dbo.yc_1_LietKeDia
Chc nng
Lit k ton b a
Qun l ca hng a
Trang 18
Bo co bi tp ln
dbo.yc_1a_LietKeDia
Lit k a theo th loi
dbo.yc_1b_LietKeDia
Lit k a theo ho n
dbo.yc_2_LietKeKH
Lit k cc khch hng mua hng

Lit k
( sp xp)
khch hng
Lit k cc khch hng mua hng theo
dbo.yc_2a_LietKeKH
dbo.yc_3_NhanVien
dbo.yc_4_DoanhThu
Tng doanh thu bn hng
dbo.yc_4a_DoanhThu
Tng doanh thu bn hng theo thng
dbo.yc_4b_DoanhThu
Tng doanh thu bn hng theo nm
Bo co
10
dbo.yc_5_BaoCaoChung
Bo co chung
tng hp
11
dbo.yc_5a_BaoCaoThang
Bo co theo thng
nhp, xut
12
dbo.yc_5b_BaoCaoNam
Bo co theo nm
13
dbo.yc_6_DiaKoBanDuoc
14
dbo.yc_6a_DiaKoBanDuoc
15
dbo.yc_6b_DiaKoBanDuoc
16
dbo.yc_7_DiaBanChay
Thng k 10 a bn chy nht
17
dbo.yc_7a_DiaBanChay
Thng k 10 a bn chy nht theo thng
18
dbo.yc_7b_DiaBanChay
Thng k 10 a bn chy nht theo nm
khu vc ( sp xp)
Lit k
Lit k 3 nhn vin bn hng thu c

doanh thu nhiu nht
nhn vin
Thng k
doanh thu
Thng k cc a khng bn c trong

ton b thi gian
Thng k
s ph bin
ca a
Thng k cc a khng bn c theo

thng
Thng k cc a khng bn c theo
nm
3) Chi tit cc Store Procedure thc thi yu cu

STT
Chc nng
Store Procedure
Qun l ca hng a
Trang 19
Bo co bi tp ln
Lit k
1
thng tin
ton b a
Lit k
2
thng tin
a theo th
loi
Lit k
3
thng tin
a theo
ho n
Lit k cc
4
khch hng
mua
hng
Lit k cc
khch hng
mua
hng theo
khu vc
Top 3 nhn
vin bn
hng c
SELECT Dia.MaDia, Dia.TenDia, TheLoaiDia.TenTL,

SUM(CTiet_HD.SoLuong) AS SoLuong, CTiet_HD.DonGia,
LoaiPhieu.TenLoai
FROM CTiet_HD INNER JOIN
Dia ON CTiet_HD.MaDia = Dia.MaDia INNER JOIN
TheLoaiDia ON Dia.MaTL = TheLoaiDia.MaTL INNER JOIN
HoaDon ON CTiet_HD.MaHD = HoaDon.MaHD INNER JOIN
LoaiPhieu ON HoaDon.MaLoai = LoaiPhieu.MaLoai
GROUP BY Dia.MaDia, Dia.TenDia, TheLoaiDia.TenTL,
CTiet_HD.DonGia, HoaDon.MaLoai, LoaiPhieu.TenLoai
ORDER BY HoaDon.MaLoai
LoaiPhieu.TenLoai
WHERE TheLoaiDia.TenTL = @TenTL
LoaiPhieu.TenLoai
WHERE LoaiPhieu.TenLoai = @LoaiHD
SELECT KhachHang.MaKH, KhachHang.TenKH, KhachHang.Dchi_KH,
KhachHang.SDT, KhachHang.Email,
SUM(CTiet_HD.SoLuong*CTiet_HD.DonGia) AS TongTien
KhachHang ON HoaDon.MaKH = KhachHang.MaKH
WHERE HoaDon.MaLoai = 'PX'
GROUP BY KhachHang.MaKH, KhachHang.TenKH,
KhachHang.Dchi_KH, KhachHang.SDT, KhachHang.Email
ORDER BY TongTien DESC
SELECT KhachHang.MaKH, KhachHang.TenKH, KhachHang.Dchi_KH,
KhachHang.SDT, KhachHang.Email,
SUM(CTiet_HD.SoLuong*CTiet_HD.DonGia) AS TongTien
KhachHang ON HoaDon.MaKH = KhachHang.MaKH
WHERE HoaDon.MaLoai = 'PX' AND KhachHang.Dchi_KH = @DiaChi
GROUP BY KhachHang.MaKH, KhachHang.TenKH,
KhachHang.Dchi_KH, KhachHang.SDT, KhachHang.Email
SELECT TOP(3) NhanVienBH.MaNV, NhanVienBH.TenNV,
NhanVienBH.Dchi_NV, SUM(CTiet_HD.SoLuong*CTiet_HD.DonGia)
AS TongTien
Qun l ca hng a
Trang 20
Bo co bi tp ln
doanh thu
ln nht
7
Tng doanh SELECT SUM(ch.SoLuong*ch.DonGia) AS DoanhThu

FROM CTiet_HD ch
thu bn a
Doanh thu
bn hng
theo thng
Doanh thu
bn hng
theo nm
10
11
NhanVienBH ON HoaDon.MaNV = NhanVienBH.MaNV

GROUP BY NhanVienBH.MaNV, NhanVienBH.TenNV,
NhanVienBH.Dchi_NV
Bo co
chung
Bo co
theo thng
SELECT SUM(ch.SoLuong * ch.DonGia) AS DoanhThu

FROM CTiet_HD AS ch INNER JOIN
HoaDon ON ch.MaHD = HoaDon.MaHD
WHERE DATEPART(MONTH,HoaDon.NgayBan) = @Thang AND
DATEPART(YEAR,HoaDon.NgayBan) = @Nam
SELECT SUM(ch.SoLuong * ch.DonGia) AS DoanhThu
FROM CTiet_HD AS ch INNER JOIN
HoaDon ON ch.MaHD = HoaDon.MaHD
WHERE DATEPART(YEAR,HoaDon.NgayBan) = @Nam
SELECT Dia.MaDia, Dia.TenDia, tld.TenTL, A.TongNhap,
B.TongXuat
FROM Dia INNER JOIN TheLoaiDia tld ON tld.MaTL = Dia.MaTL
LEFT JOIN
(
SELECT Dia.MaDia,
SUM(CTiet_HD.SoLuong*CTiet_HD.DonGia) AS TongNhap
HoaDon ON CTiet_HD.MaHD = HoaDon.MaHD
HERE (HoaDon.MaLoai = 'PN')
GROUP BY Dia.MaDia
) A ON A.MaDia = Dia.MaDia LEFT JOIN
(
SELECT Dia.MaDia,
SUM(CTiet_HD.SoLuong*CTiet_HD.DonGia) AS TongXuat
WHERE (HoaDon.MaLoai = 'PX')
GROUP BY Dia.MaDia
) B ON B.MaDia = A.MaDia
A.TongNhap, B.TongXuat
TheLoaiDia ON Dia.MaTL = TheLoaiDia.MaTL LEFT JOIN
(
SELECT Dia.MaDia,
WHERE (HoaDon.MaLoai = 'PN')
GROUP BY Dia.MaDia
(
SELECT Dia.MaDia,
Qun l ca hng a
Trang 21
Bo co bi tp ln
12
13
14
Bo co
theo nm
a khng
bn c
a khng
bn c
theo thng
GROUP BY Dia.MaDia
WHERE DATEPART(MONTH,HoaDon.NgayBan) = @Thang AND
TheLoaiDia ON Dia.MaTL = TheLoaiDia.MaTL LEFT JOIN
(
SELECT Dia.MaDia,
WHERE (HoaDon.MaLoai = 'PN')
GROUP BY Dia.MaDia
(
SELECT Dia.MaDia,
GROUP BY Dia.MaDia
WHERE DATEPART(YEAR,HoaDon.NgayBan) = @Nam
SELECT TheLoaiDia.MaTL, Dia.MaDia, Dia.TenDia,
TheLoaiDia.TenTL, HoaDon.NgayBan
FROM Dia INNER JOIN
CTiet_HD ON Dia.MaDia = CTiet_HD.MaDia INNER JOIN
WHERE Dia.MaDia NOT IN
(
SELECT CTiet_HD.MaDia
)
FROM Dia INNER JOIN
(
Qun l ca hng a
Trang 22
Bo co bi tp ln
a khng
15
bn c
theo nm
Top 10 a
16
bn chy
nht
Top 10 a
17
bn chy
nht theo
thng
Top 10 a
18
bn chy
nht theo
nm

) AND DATEPART(MONTH,HoaDon.NgayBan) = @Thang AND
FROM Dia INNER JOIN
(
) AND DATEPART(YEAR,HoaDon.NgayBan) = @Nam
SELECT TOP(10) Dia.MaDia, Dia.TenDia, TheLoaiDia.TenTL,
LoaiPhieu.TenLoai
ORDER BY SoLuong DESC
LoaiPhieu.TenLoai
WHERE HoaDon.MaLoai = 'PX' AND
DATEPART(MONTH,HoaDon.NgayBan) = @Thang AND
LoaiPhieu.TenLoai
WHERE HoaDon.MaLoai = 'PX' AND
Qun l ca hng a
Trang 23
Bo co bi tp ln
PH LC 1: K HOCH TNG TH
I) GII THIU
1. Tn ti: Qun l ca hng a (Software + Games)
2. Thng tin v nhm xy dng ti
-
Dng Th nh Nguyt - Nhm trng
Trn Phc Hon
L Ngc Ha
Nguyn Minh Khim
Ha Thanh Tng
3. Mc ch ca ti liu:
-
K hoch tng th nh ngha cc hot ng pht trin phn mm v cc giai on trong

d n xy dng phn mm qun l ca hng a.
K hoch tng th miu t ton b k hoch c s dng cho i ng pht trin h thng.
Cc chi tit cng vic c th c miu t r hn trong k hoch chi tit hng thng.
4. Phm vi ti liu
-
Ti liu ny phc v cho cc i tng l ngi s dng chng trnh qun l cc ca

hng a phn mm, tr chi va v nh.
5. M t ti liu
Ti liu bao gm cc phn chnh sau:
-
Gii thiu chung: gii thiu tng quan v ti liu.
Ni dung: K hoch xy dng v pht trin CSDL phn mm.
Qun l ca hng a
Trang 24
Bo co bi tp ln
5.1. Gii thiu chung
-
Thi gian v ni dung cng vic:
Ngy thng ghi c th v r rng trong tng thi im cng vic.
Ni dung khi qut nhng th hin chnh xc cng vic cn lm trong thi gian .
5.2. Sn phm:
-
c vit ra mt cch c th bng cc bin bn hoc bo co trc tip t ngi c trch

nhim.
Trch nhim v Kt qu:
Ngi c giao trch nhim thc hin ng v nhit tnh vi cng vic cn lm.
Kt qu c yu cu theo mt mc nht nh, c nh gi bi ton nhm thng

qua nhm trng.
Bo co ton b v thit k Slide gii thiu sn phm.
5.3. K hoch xy dng v pht trin phn mm

5.3.1. Trin khai kho st cc ca hng a
- Tham kho cch sp xp, phn chia cc th loi a khc nhau.
- Thu thp c cc thng tin nh cch nh m s, mc ch, cch thc,
- Tm hiu cc ha n nhp xut a.
5.3.2. Nghin cu cc kho st
- Sau khi kho st, tp trung a ra cc c t yu cu phn tch thit k.
- Ton nhm s nghin cu cc mc ca cc kho st thng k cc b, cc thuc tnh v
quan h tin ti xy dng lc thc th lin kt v m hnh quan h.
Qun l ca hng a
Trang 25
Bo co bi tp ln
- Cc kho st cng s c phn chia r rng c th tng phn, cc thnh vin s nghin
cu cc phn khc nhau v thng nht bi nhm trng.
5.3.3. Thit k C s d liu
- Thnh vin c giao trch nhim lp trnh chnh s nh hng da theo cc nghin cu
kho st.
- Cc thnh vin cn li s ng thi theo di, gp , chnh sa v b sung cho CSDL, m
bo tnh chnh xc ti a cho CSDL s c to.
5.3.4. Bo co v thit k Slide Show
- Bo co:
o Cc thnh vin c trch nhim vi tng phn s a ra cc bo co chi tit ca
phn .
o
Bo co s c tng hp v kt hp gii thch song song vi CSDL c to.
Di s tng hp ca nhm trng v gp , b sung ca thnh vin, bo co s c kt

hp mt cch hon thin nht.
- Slide Show:
o S c thit k bng PowerPoint 2003.
o Da theo bo co chi tit c a ra, chn lc cc thnh phn chnh, mc tiu
a ra nhng ni bt ca CSDL c to.
o Nhm trng tng hp v gp ca thnh vin, Slide s c hon thin.
Qun l ca hng a
Trang 26
Bo co bi tp ln
II) Ni dung
Qun l ca hng a
Trang 27
Bo co bi tp ln
PH LC 2: BIN BN KHO ST
Hm nay, vo lc 13 gi ngy 05 thng 02 nm 2011, Nhm 1 tin hnh i kho st
phc v cho bi tp ln mn n CSDL ti:
-
Ca hng: Heaven.
a ch: 668 ng Lng ng a H Ni.
I) Thnh phn kho st:

-
Nguyn V Vi Vi - Nhn vin bn hng.
Ha Thanh Tng - i din nhm.
Nguyn Minh Khim - i din nhm.
II) Ni dung kho st:

1. Thng tin chung:
H tn: Nguyn V Vi Vi.
Nm sinh: 1985.
Ngh nghip: Nhn vin ca hng a.
a ch thng tr: 186 Yn Ho Cu Giy H Ni.
2. Tin hnh kho st:

2.1. Hnh thc kinh doanh:
2.1.1. Bn l:
- Bn l trc tip tng sn phm n ngi dng cui.
- Khch hng chnh: C nhn.
- S lng: 1 n 20 a.
- Hnh thc thanh ton: Tin mt.
- Cch thc mua hng:
o Lin h qua in thoi (S lng t 10 a tr ln).
o n trc tip ti ca hng.
- Phng thc a hng:
Qun l ca hng a
Trang 28
Bo co bi tp ln
o Chuyn hng tn ni.
o Khch nhn a ngay ti ca hng.
- Gi bn: ph thuc vo gi bn l tng loi a (8.000 200.000 VND/1 a).
- nh gi phng thc bn hng:
o u im: Ri ro thp, vn t, d qun l.
o Nhc im: Hng bn khng c nhiu, nhiu i th cnh tranh, li nhun
thp.
2.1.2. Bn trc tuyn:
- Bn trc tuyn (sell online) l hnh thc kh mi ca ca hng va c th trc tip qung
b cng nh bn hng thng qua Internet.
- Khch hng chnh: C nhn, Cng ty va v nh,
- S lng: 20 n 1000 a.
- Cch thc mua hng:
o t hng trn website ca ca hng.
o t hng thng qua shop ca ca hng trn cc website thng mi in t nh:
chodientu.vn, vatgia.com, 123mua.vn, enbac.com,
- Hnh thc thanh ton:
o Tin mt: Tr 100% chi ph + tin vn chuyn.
o Chuyn khon ngn hng: Tr trc 70% chi ph + tin vn chuyn.
o V in t online: V-Cash, Nganluong.vn,
- Phng thc a hng:
o Chuyn hng tn ni.
o Khch nhn a ngay ti ca hng.
- Gi bn: ph thuc vo s lng a (8.000 200.000 VND/1 a).
o T 10 n 50 a: gim 5%.
o T 50 n 500 a: gim 10%.
o Trn 500 a: gim 15%.
Qun l ca hng a
Trang 29
Bo co bi tp ln
- nh gi phng thc bn hng:
o u im: C th khng cn mt bng, tit kim thi gian cho khch hng, hng
bn c nhiu, d qun l.
o Nhc im: Ri ro tng i cao, vn nhiu, nhiu cnh tranh.
2.2. Mt hng kinh doanh:
- a tr chi dnh cho cc th loi my khc nhau c nh m G+Tn th loi bao gm:
o a PC: dnh ci t cho cc my vi tnh c nhn.
o a PS2: dnh cho dng my PlayStation 2 ca Sony.
o a Xbox: dnh cho dng my Xbox 360, Xbox Live ca Microsoft.
o a Wii: dnh cho dng my Wii ca Nintendo.
- a phn mm gm cc phin bn h iu hnh v cc phn mm thng dng c nh
m S+Tn th loi bao gm:
o a h iu hnh: gm cc phin bn h iu hnh Windows t Windows 98 n
Windows 7 v cc phin bn Linux t ph thng: Fedora, Ubuntu,
o a phn mm tin ch: gm cc a phn mm tin ch khi vn phng nh b
Microsoft Office, HTKK Thu, v cc phn mm chuyn dng phc v khi
thit k nh Adobe Photoshop, AutoCAD, CorelDRAW,
o Ngoi ra gm cc a phn mm ph thng khc.
2.3. Gi trung bnh cc loi a:
i vi cc loi a bn quyn bao gm c key active v cc h tr i km gi kh cao so vi
mt bng chung nn rt kh bn. Cc loi a Games c bn kh chy v phc v nhu cu thit
yu v gii tr ca khch hng.
o a bn quyn: 100.000 200.000 VND. Gi ch gm b a. Sn phm cn c
thm key active v ng k c hng sn xut chng nhn ch bn cho duy nht
mt cc nhn hoc mt tp th (phin bn m rng).
o a ph thng: 8.000 VND 100.000 VND. Cc a c b kho c ghi li,
vi loi c ghi trn a trng loi tt, bn gi s cao hn.
Qun l ca hng a
Trang 30
Bo co bi tp ln
2.4. Cc ngun cung cp a:
o Trc tip t cc hng sn xut hoc i l nhp v cc a bn quyn km cc
giy ng k sn phm.
o T cc ch u mi, hoc trc tip ly trn Internet cc sn phm c b kho
ghi ra a.
2.5. Thu nhp bnh qun hng thng: 20.000.000 VND.
2.6. Yu cu t ra:
-
Khch hng a ra cc thng tin v a mun mua.
Ngi bn hng s tip thu thng tin v tm theo th loi, tnh nng ca a.
Nu khch hng mua a, d liu s c nhp vo CSDL i vi tng loi ho n bn

l hoc bn trc tuyn.
Ch ca hng s theo di trn CSDL c lu.
2.7. Yu cu qun l:
-
Qun l a nhp vo:
Th loi a.
S lng.
Gi tin.
Qun l a bn ra:
Th loi a.
S lng.
Gi tin.
Bo co thng k hng thng:
S lng a nhp vo v bn ra.
S lng a lu tr trong kho.
Tng tin nhp xut v doanh thu.
II) Mc tiu ca ca hng:

Qun l ca hng a
Trang 31
Bo co bi tp ln
-
M rng kh nng i vi cc loi a bn quyn c tr gi, in hnh cc phn mm

Anti-virus v H iu hnh vi cc hnh thc key bn quyn gi r cho c nhn khng bao
gm hp sn phm gim chi ph.
Tp trung thm vo vic hiu chnh website cho d dng ng k mua a, tng cng
qung b trc tuyn m rng vng kinh doanh trn ton quc, t s tng thm khch
hng tim nng.
III) Kt lun ca nhm kho st

Sau khi t ra cc cu hi cho nhn vin bn hng, nhm tng hp trn gm cc mt
hng, hot ng c bn ca ca hng. Ca hng phn chia cc th loi a, nh m s rt r
rng, cc ho n nhp xut cng c lu tr cn thn, c th d dng kim sot cng nh
thng k doanh thu r rng.
Bin bn c tng hp v kt thc vo 17 gi cng ngy./.
CH K CA NHN VIN CA HNG
Nguyn V Vi Vi
CH K I DIN NHM KHO ST
Ha Thanh Tng
Qun l ca hng a
Trang 32
Bo co bi tp ln
PH LC 3: NH GI THNH VIN
STT
Thnh vin
Dng Th
nh Nguyt
Cng vic
Ti liu c t
Tt
Xy dng bi ton
Tt
V m hnh thc th lin kt
Tt
Xc nh m hnh quan h
Tt
C s d liu chnh
Tt
Slide bo v
Tt
Bo co bi tp ln
Tt
L Ngc Ho Ti liu c t
Nguyn
Minh Khim
Tt
Slide bo v
Tt
Xy dng bt theo i s quan h
Tt
Xy dng bi ton theo SQL
Cha t
Slide bo co
Tm n
Bin bn kho st
Tt
Ti liu c t
Tt
Xy dng bt theo i s quan h

Trn Phc
Hon
Ghi ch
Tt
Xc nh cc thuc tnh, thc

th
Xc nh cc thuc tnh, thc

th
nh gi
Cha y
Cha t
V m hnh thc th lin kt
Tt
C s d liu chnh
Tt
Slide bo v
Tt
Slide bo co
Tm n
Hon h tr: Tt
Hon h tr: Tt
Nguyt h tr: Tt
Qun l ca hng a
Trang 33
Bo co bi tp ln
Ha Thanh
Tng
Bo co bi tp ln
Tt
Bin bn kho st
Tt
Ti liu c t
Tt
Xy dng bi ton
Tt
Xc nh m hnh quan h
Tt
Xy dng bi ton theo SQL
Cha t
Nguyt h tr: Tt
Qun l ca hng a
Trang 34

SQL Server - Quản lý cửa hàng đĩa

Uploaded by

Copyright:

Available Formats

SQL Server - Quản lý cửa hàng đĩa

Uploaded by

Document Information

Original Description:

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

SQL Server - Quản lý cửa hàng đĩa

Uploaded by

Copyright:

Available Formats

Bo co bi tp ln

II. YU CU TIN HC HA.................................................................................................................8

III. GII QUYT VN .....................................................................................................................9

2) Cc th tc m t chc nng c yu cu......................................................................................18

PH LC 1: K HOCH TNG TH....................................................................................................24

II) Mc tiu ca ca hng:.....................................................................................................................31

Cc loi a, hiu phng thc phn loi a.

Phm vi nghin cu: trong mt ca hng bn a c th.

Chng I: Kho st h thng.

Chng II: M hnh thc th lin kt E-R v M hnh quan h.

Chng III: C s d liu SQL Server 2005.

4. Nhng thut ng v cm t vit tt

M hnh thc th lin kt

CHNG I: KHO ST H THNG

a PC: dnh ci t cho cc my vi tnh c nhn.

a PS2: dnh cho dng my PlayStation 2 ca Sony.

a Xbox: dnh cho dng my Xbox 360, Xbox Live ca Microsoft.

a Wii: dnh cho dng my Wii ca Nintendo.

a phn mm gm cc phin bn h iu hnh v cc phn mm thng dng

c nh m S+Tn th loi bao gm:

a h iu hnh: gm cc phin bn h iu hnh Windows t Windows 98 n

a phn mm tin ch: gm cc a phn mm tin ch khi vn phng nh b

Ngoi ra gm cc a phn mm ph thng khc.

Ho n c trin khai lm 2 loi chnh: Nhp hng v Xut hng.

2.3. Mc tiu ca c s d liu

y cc chc nng c bn nhp xut d liu cho tng bng.

Cng tin hng: ..

(K, ghi r h tn)

(K, ghi r h tn)

Cng tin hng: ..

Ngi giao hng

Ngi nhn hng

(K, ghi r h tn)

(K, ghi r h tn)

Cng tin hng: ..

(K, ghi r h tn)

(K, ghi r h tn)

Lit k thng tin a:

Lit k nhn vin theo doanh thu.

Thng k tng doanh thu:

Bo co chung v tng nhp, xut:

Lit k cc a cha bn.

Lit k cc a bn chy nht.

III. GII QUYT VN

M hnh ha nghip v: Da trn kho st thc trng, m hnh ha cc nghip v bng

Thit k: T kt qu cc m hnh quan h, trin khai thit k CSDL t c cc

CHNG II: M HNH THC TH LIN KT E-R

Bc 3: M hnh thc th lin kt.

II. K HIU S DNG

(min,max): Rng buc lin kt.

III. P DNG BI TON

Nhn vin bn hng.

Loi phiu xut nhp.

S in thoi khch hng

NhanVienBH (Nhn vin bn hng):

TheLoaiDia (Th loi a):

HoaDon (Ho n):

CTiet_HD (Chi tit ho n):

Bc 3: M hnh thc th lin kt