Trường ĐH Công Nghiệp Hà Nội

Khoa CNTT

TRƯỜNG ĐẠI HỌC CÔNG NGHIỆP HÀ NỘI
KHOA CÔNG NGHỆ THÔNG TIN
---o0o---

BÁO CÁO BÀI TẬP LỚN

MÔN: HỆ QUẢN TRỊ CƠ SỞ DỮ LIỆU

ĐỀ TÀI:

XD phần mềm quản lý hệ thống bán hàng trong
siêu thị bán lẻ
Giảng viên hướng dẫn: Phan Văn Viên
Lớp: HTTT1 – K6
Nhóm: 13
STT
1

Họ tên
Trần Tuấn Anh

Đ1

2

Lê Thị Thúy Hoài

3

Đỗ Thanh Huyền

4

Nguyễn Thị Nhật Lệ

5

Lộc Thị Uyên

Hà Nội 12-2012

K6_HTTT1_Nhóm 13

Trang 1

Đ2

Đ3

Đ4

KL


Trường ĐH Công Nghiệp Hà Nội

Khoa CNTT


Mục lục

LỜI NÓI ĐẦU
Quản lý một cửa hàng, một công ty, hay một siêu thị là một trong những công
việc hết sức phức tạp và khó khăn nếu quản lý bằng tay. Chính vì vậy, công nghệ thông
tin với những công cụ thích hợp, được phát triển, xây dựng để hỗ trợ cho công tác quản
lý này.
Các công cụ được phát triển để hỗ trợ quản lý hiện nay như về phía cơ sở dữ liệu
có SQL Server 2008, My SQL, Access,... Từ các cơ sở dữ liệu xây dựng bằng các hệ quản
trị cơ sở dữ liệu trên, ta có thể dễ dàng quản lý các vấn đề đặt ra cho kế toán, quản lý
một cửa hàng một doanh nghiệp dựa trên các công cụ có sẵn của hệ quản trị cơ sở dữ
liệu, hoặc là sử dụng các ngôn ngữ lập trình khác như VB, C#, VB.NET,… để sử dụng cơ
sở dữ liệu đó.
Hệ quản trị cơ sở dữ liệu SQL Server 2008 là một hệ quản trị cơ sở dữ liệu của
hãng Microsoft phát triển. Phiên bản này là một trong những phiên bản phổ biến, và
được sử dụng nhiều nhất trong các hệ thống quản lý. Chính vì vậy, với đề tài được giao,
hệ quản trị cơ sở dữ liệu SQL Server là một trong những công cụ thích hợp và hợp lý.
Đề tài quản lí của chúng em là : “Quản lí hệ thống bán hàng ở siêu thị bán lẻ”.
Với đề tài này nhóm 13 chúng em chủ yếu nghiên cứu việc bán hàng trong một siêu thị
bán lẻ kết hợp với các hàm thủ tục, trigger, phân quyền cho người dùng để tiến hành
quản lí siêu thị hợp lí hơn.

K6_HTTT1_Nhóm 13

Trang 2


Trường ĐH Công Nghiệp Hà Nội


Khoa CNTT

PHẦN 1: THỰC HIỆN ĐỀ TÀI
CHƯƠNG I: PHÂN TÍCH VÀ THIẾT KẾ BÀI TOÁN
Hiện nay có rất nhiều các hệ thống siêu thị vừa và nhỏ, với rất nhiều các loại
mặt hàng trong đó siêu thị Thành là một trong các siêu thị bán lẻ các loại mặt hàng
dân dụng. Vấn đề đặt ra đổi với các nhà quản lí của siêu thị đó là làm sao để quản
lí được hệ thống bán hàng trong siêu thị một cách tốt nhất. Người ta xây dựng, tổ
chức hệ thống thông tin để theo dõi, quản lí hàng hóa và phục vụ việc mua hàng
của khách hàng (ở đây chỉ xét bài toán quản lí hàng hóa và bán hàng).
Qua việc tìm hiểu các tài liệu và thông tin trên mạng chúng ta thấy có rất
nhiều cách để quản lí việc mua bán trong siêu thị bán lẻ, nhưng với công cụ đắc lực
là SQL SERVER 2008 được sử dụng rất phổ biến và sẽ giúp cho chúng ta quản lí
được công việc bán hàng một cách có hệ thống, bảo mật và chặt chẽ nhất.
Trong bài toán xây dựng và thiết kế phần mềm quản lý hệ thống bán hàng
trong siêu thị bán lẻ này, chúng em xây dựng mô hình cơ sở dữ liệu Quản lý Siêu
thị (QLST) quản lý các thông tin về khách hàng, nhân viên, các mặt hàng và các
hóa đơn hàng của khách hàng. Cơ sở dữ liệu QLST gồm 8 bảng đưa ra thông tin về
các mặt hàng, nhà cung cấp, khách hàng, nhân viên, hóa đơn hàng, và thông tin về
các mặt hàng được nhập.

K6_HTTT1_Nhóm 13

Trang 3


Trường ĐH Công Nghiệp Hà Nội

Khoa CNTT


CHƯƠNG II: CÀI ĐẶT TRUY XUẤT CƠ SỞ DỮ LIỆU
TRONG SQL SERVER 2008
Từ quá trình phân tích và thiết kế bài toán ta tiến hành thực hiện trên SQl
Server 2008.
I.
1.

Tạo cơ sở dữ liệu
Tạo database:

USE master
GO
IF EXISTS (select * from SYSDATABASES
where name='QLST')
DROP database QLSieuThi
GO
create database QLST
on
(name=QLST,
Filename='C:\Program Files\Microsoft SQL
Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\QLST.mdf',
size=50mb, maxsize=200mb, filegrowth=10mb)
log on
(name=QLST_log,
filename='C:\Program Files\Microsoft SQL
Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\QLST_Data.mdf',
size=10mb, maxsize=unlimited, filegrowth=10mb)
GO
−


Tạo các bảng:

Để quản lí chúng ta sẽ xây dựng một cơ sở dữ liệu gồm 8 bảng như sau:
USE QLST
GO
--tạo bảng nhà cung cấp (nhacc)-create table nhacc(
manhacc nvarchar(5) PRIMARY KEY,
tennhacc nvarchar(100) NOT NULL,

K6_HTTT1_Nhóm 13

Trang 4


Trường ĐH Công Nghiệp Hà Nội

Khoa CNTT
diachi nvarchar(200) NOT NULL,
dienthoai nvarchar(20) NOT NULL

)
--tạo bảng hàng hóa (hanghoa)-create table hanghoa(
mahang nvarchar(5) PRIMARY KEY,
tenhang nvarchar(100) NOT NULL,
manhacc nvarchar(5) not null,
dvtinh nvarchar(10) NOT NULL,
giahang money not null,
khuyenmai real not null,
foreign key(manhacc)
references nhacc(manhacc)

)
--tạo bảng khách hàng(khachhang)-create table khachhang(
makh nvarchar(5) primary key,
tenkh nvarchar(50) not null,
diachi nvarchar(100) not null,
dienthoai nvarchar(20) not null
)
--tạo bảng nhân viên (nhanvien)-create table nhanvien(
manv nvarchar(5) primary key,
tennv nvarchar(50) not null,
ngaysinh datetime not null,
diachi nvarchar(100) not null,
dienthoai nvarchar(20) not null,
luong money not null
)
--tạo bảng hóa đơn (hoadon)-create table hoadon(
mahd nvarchar(5) PRIMARY KEY,
ngayhd datetime NOT NULL,
makh nvarchar(5) NOT NULL,
manv nvarchar(5) NOT NULL,
foreign key(makh)
references khachhang(makh),
foreign key(manv)
references nhanvien(manv)
)

K6_HTTT1_Nhóm 13

Trang 5



Trường ĐH Công Nghiệp Hà Nội

Khoa CNTT

--tạo bảng chi tiết hóa đơn (cthoadon)-create table cthoadon(
mahd nvarchar(5),
mahang nvarchar(5),
soluong smallint NOT NULL,
dongia money not null,
primary key(mahd,mahang),
foreign key (mahd)
references hoadon(mahd),
foreign key(mahang)
references hanghoa(mahang)
)
--tạo bảng phiếu nhập (pnhap)-create table pnhap(
mapn nvarchar(5) PRIMARY KEY,
ngaynhap datetime NOT NULL,
mahd nvarchar(5),
foreign key(mahd)
references hoadon(mahd)
)
--tạo bảng chi tiết phiếu nhập (ctpnhap)-create table ctpnhap(
mapn nvarchar(5),
mahang nvarchar(5),
slnhap smallint NOT NULL,
dgnhap money not null,
PRIMARY KEY (mapn,mahang),
foreign key (mapn)

references pnhap(mapn),
foreign key(mahang)
references hanghoa(mahang)
)
GO

Sau khi chạy các câu lệnh, ta được 8 bảng như sau:
a) Bảng nhà cung cấp (nhacc):

K6_HTTT1_Nhóm 13

Trang 6


Trường ĐH Công Nghiệp Hà Nội
b)

Bảng hànghóa (hanghoa):

c)

Bảng khách hàng (khachhang):

d)

Bảng nhân viên (nhanvien):

e)

Bảng hóa đơn (hoadon):


f)

Bảng chi tiết hóa đơn (cthoadon):

K6_HTTT1_Nhóm 13

Khoa CNTT

Trang 7


Trường ĐH Công Nghiệp Hà Nội

Khoa CNTT

g)

Bảng phiếu nhập (pnhap):

h)

Bảng chi tiết phiếu nhập (ctpnhap):

2.

Nhập dữ liệu vào bảng (dùng lệnh INSERT INTO):
USE QLST
GO
INSERT INTO nhacc VALUES

('CC01','Pham Nguyen','Quan 12 - HCM','0835926885'),
('CC02','Trang Tien','Đong Đa - Ha Noi','0436462740'),
('CC03','Ngoi Sao','Tu Liem - Ha Noi','0462656566'),
('CC04','MASAN Group','Tan Phu - HCM','0438247773')
INSERT INTO hanghoa VALUES
('TL100','Tra lai Tam Chau 100g','CC01','Hop','26900','0'),
('KS150','Keo sua Caramen 150g','CC02','Goi','29500','0'),
('BG195','Banh gau Koala 195g','CC03','Hop','50200','0'),
('NT500','Nuoc
tuong
Tam
Thai
Tu
500ml','CC04','Chai','20000','0'),
('TC50','Tra
Thai
Nguyen
Cozy
50g','CC01','Hop','25700','0'),
('NM250','Nuoc mam Chinsu 250ml','CC04','Chai','18500','0')

K6_HTTT1_Nhóm 13

Trang 8


Trường ĐH Công Nghiệp Hà Nội

Khoa CNTT


INSERT INTO khachhang VALUES
('KH01','Tran Van An','Go Vap - HCM','92857295'),
('KH02','Le My Le','Cau Giay - Ha Noi','57395799'),
('KH03','Nguyen Nhu Ngoc','Tay Ho - Ha Noi','29572935'),
('KH04','Hoang Quoc Dat','Quan 1 - HCM','94728947')
INSERT INTO nhanvien VALUES
('NV01','Nguyen Thi Nga','1989-02-18','Binh Thanh HCM','56285695','5000000'),
('NV02','Pham Thi Linh','1988-07-11','Hai Ba Trung - Ha
Noi','52652593','7000000'),
('NV03','Le Ngoc Mai','1989-10-22','Kim Ma - Ha
Noi','37598589','7000000'),
('NV04','Tran Mai Chi','1988-01-13','Quan 1 HCM','95789295','5000000')
INSERT INTO hoadon VALUES
('D01','2000-01-02','KH01','NV04'),
('D02','2000-02-05','KH04','NV01'),
('D03','2000-02-01','KH03','NV02'),
('D04','2000-01-06','KH02','NV01')
INSERT INTO cthoadon VALUES
('D03','BG195','5','50200'),
('D01','NT500','3','20000'),
('D02','TC50','10','25700'),
('D04','KS150','8','29500')
INSERT INTO pnhap VALUES
('N01','2000-02-01','D01'),
('N02','2000-01-08','D04'),
('N03','2000-02-02','D03'),
('N04','2000-01-06','D01')
INSERT INTO ctpnhap VALUES
('N01','NT500','500','19500'),
('N03','BG195','100','50000'),

('N02','KS150','150','29000'),
('N04','NT500','500','19500')
GO

Sau khi chạy các cậu lệnh chúng ta có thông tin chi tiết trong các bảng như sau:
Các thông tin trong bảng nhà cung cấp:

K6_HTTT1_Nhóm 13

Trang 9


Trường ĐH Công Nghiệp Hà Nội

Khoa CNTT

•

Các thông tin trong bảng hàng hóa:

•

Các thông tin trong bảng khách hàng:

•

Các thông tin trong bảng nhân viên:

• Các thông tin trong bảng hóa đơn:


K6_HTTT1_Nhóm 13

Trang 10


Trường ĐH Công Nghiệp Hà Nội

Khoa CNTT

• Các thông tin trong bảng chi tiết hóa đơn:

•

Các thông tin trong bảng phiếu nhập:

•

Các thông tin trong bảng chi tiết phiếu nhập:

3. Mô hình quan hệ cơ sở dữ liệu

Hình 1: Mô hình quan hệ cơ sở dữ liệu QLSieuThi
K6_HTTT1_Nhóm 13

Trang 11


Trường ĐH Công Nghiệp Hà Nội
II.
Các lệnh, thủ tục truy

1. Xây dựng khung nhìn VIEW

Khoa CNTT

xuất dữ liệu

Một khung nhìn (View) là một tập gồm các dòng và các cột, và có thể được xem
như là một bảng ảo trong cơ sở dữ liệu có nội dung được định nghĩa thông qua một truy
vấn (câu lệnh SELECT).
Dưới đây là một số cậu lệnh minh họa cho view:
a)

Tạo view vw_hanghoa gồm mã hàng, tên hàng liệt kê danh sách các mặt hàng trong
bảng hanghoa

USE QLST
GO
create view vw_hanghoa
AS
SELECT mahang, tenhang from hanghoa
GO

Sau khi chạy view ta dc bảng sau:

b)

Tạo view vw_tt_hanghoa thống kê những mặt hàng nào chưa được mua

USE QLST
GO

create view vw_tt_hanghoa
AS
select mahang, tenhang
from hanghoa
where not exists (select mahang from cthoadon
where
mahang=hanghoa.mahang)
GO

Những mặt hàng chưa được mua:
K6_HTTT1_Nhóm 13

Trang 12


Trường ĐH Công Nghiệp Hà Nội

c)

Khoa CNTT

Tạo view vw_thongke thống kê trong năm 2000, mỗi mặt hàng trong mỗi tháng và
trong cả năm bán được với số lượng bao nhiêu

USE QLST
GO
create view vw_thongke
as
select b.mahang, tenhang,
SUM(case MONTH(ngayhd) when 1 then b.soluong else

0 end) as Thang1,
SUM(case MONTH(ngayhd) when 2 then b.soluong else
0 end) as Thang2,
SUM(case MONTH(ngayhd) when 3 then b.soluong else
0 end) as Thang3,
SUM(case MONTH(ngayhd) when 4 then b.soluong else
0 end) as Thang4,
SUM(case MONTH(ngayhd) when 5 then b.soluong else
0 end) as Thang5,
SUM(case MONTH(ngayhd) when 6 then b.soluong else
0 end) as Thang6,
SUM(case MONTH(ngayhd) when 7 then b.soluong else
0 end) as Thang7,
SUM(case MONTH(ngayhd) when 8 then b.soluong else
0 end) as Thang8,
SUM(case MONTH(ngayhd) when 9 then b.soluong else
0 end) as Thang9,
SUM(case MONTH(ngayhd) when 10 then b.soluong else
0 end) as Thang10,
SUM(case MONTH(ngayhd) when 11 then b.soluong else
0 end) as Thang11,
SUM(case MONTH(ngayhd) when 12 then b.soluong else
0 end) as Thang12,
SUM(b.soluong) as CaNam
from(hoadon as a inner join cthoadon as b on a.mahd=b.mahd)
inner join hanghoa as c on
b.mahang=c.mahang
where YEAR(ngayhd)=2000
GROUP BY b.mahang, tenhang
GO


K6_HTTT1_Nhóm 13

Trang 13


Trường ĐH Công Nghiệp Hà Nội

Khoa CNTT

Thống kê hàng hóa:

d)

Tạo view vw_thongke_nhanvien cho biết nhân viên nào của công ty bán được số
lượng hàng nhiều nhất và số lượng hàng bán được của những nhân viên này là bao
nhiêu

USE QLST
GO
create view vw_thongke_nhanvien
as
select nhanvien.manv, tennv, SUM(soluong) as tonghang
from (nhanvien inner join hoadon
on nhanvien.manv=hoadon.manv)
inner join cthoadon
on hoadon.mahd=cthoadon.mahd
group by nhanvien.manv, tennv
having SUM(soluong)>=ALL(select SUM(soluong)
from (nhanvien inner join hoadon on

nhanvien.manv=hoadon.manv)
inner join cthoadon
on hoadon.mahd=cthoadon.mahd
group by nhanvien.manv, tennv)
GO

Thống kê nhân viên: (nhân viên bán được nhiều hàng nhất)

e)

Xóa view vw_hanghoa

DROP view vw_hanghoa
f) Cập nhật dữ liệu vào view vw_bosung_hanghoa
USE QLST
GO
create view vw_bosung_hanghoa
as

K6_HTTT1_Nhóm 13

Trang 14


Trường ĐH Công Nghiệp Hà Nội

Khoa CNTT

select mahang, tenhang, hanghoa.manhacc, dvtinh,
giahang, khuyenmai

from hanghoa full outer join nhacc on
hanghoa.manhacc=nhacc.manhacc
GO
--bổ sung thêm thông tin vào bảng hanghoa
insert into vw_bosung_hanghoa(mahang, tenhang, manhacc,
dvtinh, giahang, khuyenmai)
values ('DBS1', 'Duong sieu sach Bonsu 1kg', 'CC02', 'Goi',
'21300', '0')

Bảng hàng hóa sau khi cập nhật thông tin:

2. Thủ tục lưu trữ

Một thủ tục là một đối tượng trong cơ sở dữ liệu bao gồm một tập nhiều các câu
lệnh SQL. Sử dụng thủ tục lưu trữ trong cơ sở dữ liệu sẽ giúp tăng hiệu năng của cơ sở
dữ liệu, đơn giản hóa các thao tác, việc thực thi nhanh hơn, có thể thực hiện cùng một
yêu cầu bằng một câu lệnh đơn giản, có thể cấp phát quyền cho người sử dụng.
a) Tạo thủ tục spud_hanghoa_xoa với tham số là mahang. Trong đó cần kiểm tra các
ràng buộc dữ liệu trước khi thực hiện lệnh DELETE trong bảng hanghoa
create proc spud_hanghoa_xoa
@smahang nvarchar (5)
as
declare @serrsmg nvarchar(200)
if exists (select mahang from hanghoa where mahang=@smahang)
begin
set @serrsmg ='mã hàng ['+@smahang+'] đã có,xin xóa'
raiserror(@serrsmg ,16,1)
return
end
delete mahang from hanghoa where mahang=@smahang

go

K6_HTTT1_Nhóm 13

Trang 15


Trường ĐH Công Nghiệp Hà Nội
b)

Khoa CNTT

Xây dựng thủ tục nội tại tính số lượng đơn hàng với tên spud_nhap_tongslnhap với
hai tham số vào là tenhang, slhoadon, 1 tham số ra là tổng số lượng đã nhập trong
một hóa đơn
USE QLST
GO
create proc spud_ctpnhap_tongslnhap
@smahd nvarchar (5),
@smahang char(5),
@stenhang nvarchar(100),
@nsoluong int output
as
select cthoadon.mahang,tenhang from cthoadon
inner join hanghoa on cthoadon.mahang=hanghoa.mahang
order by cthoadon.mahang
declare @serrsmg varchar(200)
if exists (select tenhang,soluong from hanghoa.cthoadon
where tenhang=@stenhang and soluong=@nsoluong)
begin

set @serrsmg ='tên hàng,tổng số lượng nhập
['+@stenhang+@nsoluong+'] đã có,xin cấp một tên khác,số
lượng nhập khác'
raiserror (@serrsmg ,16,2)
return
end
insert cthoadon.hanghoa(mahang,tenhang,soluong)
values(@smahang,@stenhang,@nsoluong)
go
GO

c)

Xây dựng thủ tục nội tại thêm mới dữ liệu vào bảng nhacc. Trong đó phải kiểm tra
các ràng buộc trước khi thực hiện lệnh insert into manhacc phải có trong bảng
nhacc
create proc spud_nhacc_insert
@smanhacc char(5),
@stennhacc varchar(100),
@sdiachi varchar(200),
@sdienthoai varchar(20)
as
select nhacc.manhacc,tennhacc,mahang from hanghoa hh
inner join nhacc on hh.manhacc=nhacc.manhacc
order by nhacc.manhacc
declare @serrsmg varchar(200)
if exists (select tennhacc, manhacc from nhacc.hh
where tennhacc=@stennhacc and manhacc=@smanhacc)
begin


K6_HTTT1_Nhóm 13

Trang 16


Trường ĐH Công Nghiệp Hà Nội

Khoa CNTT

set @serrsmg='tên nhacc, mã
nhacc['+@stennhacc+@smanhacc+']đã có ,xin cấp thêm một tên
khác, một mã nhacc khác'
raiserror(@serrsmg,16,2)
return
end
insert into nhacc.hanghoa(manhacc,tennhacc,diachi,dienthoai)
values(@smanhacc,@stennhacc,@sdiachi,@sdienthoai)
go
d)

Xây dựng thủ tục sửa đổi hàng hóa trong bảng hanghoa với tên spud_hanghoa_sua
gồm có 4 tham số vào chính là giá trị cần thay đổi. Trong thủ tục chỉ thực hiện
UPDATE SET để cập nhật dữ liệu
create proc spud_hanghoa_sua
@smahang char(4),
@stenhang varchar(50),
@sdvtinh char(4),
@nsluong int
as
declare @serrsmg nvarchar(200)

if exists (select mahang from hanghoa
where mahang=@smahang)
begin
set @serrsmg='mã hàng ['+@smahang+']đã có ,xin sửa mã '
raiserror(@serrsmg, 16,1)
return
end
update mahang
set hoahong=null
update mahang
set hoahong=(select mahang from hanghoa )
go

e)

Xây dựng thủ tục hiển thị dữ liệu cho báo cáo hóa đơn. Các thông tin trên báo cáo
bao gồm các cột trong bảng hoadon, cthoadon, hanghoa, nhacc. Thủ tục có các
tham số để lọc dữ liệu báo cáo theo đúng mã hóa đơn nhập vào, tuy nhiên nếu khi
gọi thủ tục mà không truyền vào thì xem như hiển thị tất cả các số đặt hàng có trong
hoadon

create proc spud_hoadon_baocaohoadon
@smahd char(4)=null,
@smahang varchar(4),
@stenhang varchar(50)
as
set nocount on

K6_HTTT1_Nhóm 13


Trang 17


Trường ĐH Công Nghiệp Hà Nội

Khoa CNTT

if @smahd is null
select hd.*,ct.mahang,soluong,tennhacc from hoadon hd
inner join cthoadon ct on ct.mahd=hd.mahd
inner join hanghoa hh on hh.mahang=ct.mahang
inner join nhacc ncc on ncc.manhacc=hh.manhacc
order by ct.mahd, ct.mahang
else
select hd.*,ct.mahang,tenhang,soluong,tennhacc from hoadon hd
inner join cthoadon ct on ct.mahd=hd.mahd
inner join hanghoa hh on hh.mahang=ct.mahang
inner join nhacc ncc on ncc.manhacc=hh.manhacc
where ct.mahd=@smahd
order by mahang
exec spud_dondh_baocaodondh 'TL100'
3. Trigger

Một trigger là một đối tượng gắn liến với một bảng và được tự động kích hoạt khi
xảy ra những giao tác làm thay đổi dữ liệu trong bảng. Sử dụng trigger một cách hợp lý
trong cơ sở dữ liệu sẽ có tác động rất lớn trong việc tăng hiệu năng của cơ sở dữ liệu.
a) Tạo trigger tg_hanghoa_del thực hiện xóa mahang trong bảng hanghoa
use QLST
go
create trigger tg_hanghoa_del

on hanghoa
for delete
as
begin
declare @serrmsg char(200), @mahang char(10), @del_err int
delete hanghoa
select @mahang=mahang from hanghoa
where mahang in(select mahang from deleted)
set @del_err = @@ERROR
if @del_err <>0
begin
set @serrmsg='lỗi'
raiserror (@serrmsg,16,1)
rollback tran
end
end
go
b)

Cập nhật số lượng hàng hóa cho bảng cthoadon

use QLST
go

K6_HTTT1_Nhóm 13

Trang 18


Trường ĐH Công Nghiệp Hà Nội


Khoa CNTT

create trigger tg_cthoadon_update
on cthoadon
for update
as
if update(soluong)
update ctpnhap
set ctpnhap.slnhap=ctpnhap.slnhap-(inserted.soluongdeleted.soluong)
from (deleted inner join inserted on
deleted.mahang=inserted.mahang)
go
update cthoadon
set soluong=soluong+10
where mahang='TC50'
c)

Tạo trigger tg_kiemtra_mapn trong bảng ctpnhap để kiểm tra mapn có trong pnhap
hay không, kiểm tra mahang có trong danh sách các mahang hay không

--kiem tra sopn trong bang pnhap-USE QLST
GO
create trigger tg_insert_mapn on ctpnhap
for insert
as
begin
declare @mahang char(5),
@mahd char(5),
@serrmsg char(200)

if not exists(select I.mapn from inserted I, pnhap pn
where pn.mapn=I.mapn)
begin
rollback tran
set @serrmsg='ma phieu nhap hang khong co, nhap lai'
raiserror (@serrmsg,16,1)
return
end
end
GO
--kiem tra mahang co trong bang ctdondh chua?—USE QLST
GO
create trigger tg_insert_mahang on cthoadon
for insert
as
begin
declare @mahang char(5),

K6_HTTT1_Nhóm 13

Trang 19


Trường ĐH Công Nghiệp Hà Nội

Khoa CNTT

@mahd char(5),
@serrmsg char(200)
if not exists(select I.mahang from inserted I, pnhap pn,

cthoadon cthd
where pn.mahd=cthd.mahd and
I.mahang=cthd.mahang)
begin
rollback tran
set @serrmsg='ma hang chua co trong danh sach hoa don,
nhap lai'
raiserror (@serrmsg,16,1)
return
end
end
GO
4. Giao tác

Một giao tác (transaction) là một chuỗi hoặc một nhiều câu lệnh SQL được kết
hợp lại với nhau thành một khối công việc nhằm đảm bảo tính toàn vẹn dữ liệu và khả
năng phục hồi dữ liệu.
a) Giao tác không tường minh
−
Cập nhật dữ liệu vào bảng nhacc, thêm nhà cung cấp mới
use QLST
go
insert into nhacc
values('CC05','Tien Phat','Go Vap - HCM','29482490')
go
-- sửa đổi tên nhà cung cấp CC03
use QLST
go
update nhacc
set tennhacc='Phu Thanh'

where manhacc='CC03'
go
-- xóa nhà cung cấp CC05
use QLST
go
delete nhacungcap where manhacc='CC05'
go
b)

Giao tác tường minh

K6_HTTT1_Nhóm 13

Trang 20


Trường ĐH Công Nghiệp Hà Nội
−

Khoa CNTT

Sử dụng lệnh begin tran để chỉ định bắt đầu thực hiện giao tác thêm nhà cung cấp
mới vào bảng nhacc
set ansi_warnings off
go
select COUNT(*) as 'tong nha cung cap truoc khi them' from
nhacc
begin tran
insert into nhacc(manhacc,tennhacc,diachi,dienthoai)
values('CC06','Phu Dat','Ba Dinh - Ha Noi','95825689')

select COUNT (*) as 'tong nha cung cap sau khi them trong giao
tac' from nhacc
rollback tran
select COUNT(*) as 'tong nha cung cap hien tai' from nhacc
set ansi_warnings on

5. Phân quyền

Phân quyền đóng vai trò quan trọng trong việc bảo đảm an toàn thông tin cũng
như sự sống còn của cơ sở dữ liệu. SQL cũng cho phép chúng ta thiết lập chế độ phân
quyền cũng như bảo mật trong cơ sở dữ liệu.
a)
−

Tạo tên đăng nhập (login) và người dùng (user)
Tạo tên đăng nhập:
CREATE login [nhom13]
with password='1234'

−

Tạo tên người dùng:
CREATE
user [QLST]
for login [nhom13]

b)

Phân quyền cho user 'QLST' thao tác dữ liệu với các bảng
grant

grant
grant
grant
grant
grant
grant
grant

−

all
all
all
all
all
all
all
all

on
on
on
on
on
on
on
on

nhacc to QLST with grant option
hanghoa to QLST with grant option

dondh to QLST with grant option
ctdondh to QLST with grant option
pnhap to QLST with grant option
ctpnhap to QLST with grant option
ctpxuat to QLST with grant option
pxuat to QLST with grant option

Thao tác trên cho phép user 'QLST' có thể thực hiện các thao tác như INSERT,
UPDATE, ... trên các bảng thuộc CSDL và được phép phân quyền cho các user
khác (phân các quyền được cho).

K6_HTTT1_Nhóm 13

Trang 21


Trường ĐH Công Nghiệp Hà Nội

c)
−

Khoa CNTT

Tạo tài khoản truy nhập(login) và khách hàng(user)
Tạo tài khoản truy nhập cho khách
CREATE login [khach hang1]
with password='1122'
CREATE login [khach hang2]
with password ='3344'


−

Tạo một user tương ứng với tài khoản khách
CREATE user [KH01] for login [khach hang1]
CREATE user [KH02] for login [khach hang2]

d)
−

Phân quyền cho khách hàng
Phân quyền cho user KH01

grant select on hoadon to KH01
grant select on hanghoa to KH01 with grant option
−

Người dùng KH01 chuyển tiếp cấp phát quyền cho KH02

grant select on hanghoa to KH02
−

Thu hồi quyền đối với khách1

revoke select on hanghoa from KH01 CASCADE

PHẦN 2: BÀI TẬP BẮT BUỘC
Bài 2: Các đối tượng trong cơ sở dữ liệu
Bài 2.1: Tạo database
USE master
GO

IF exists (select * from sysdatabases where name='QLBanHang')
drop database QLBanHang
GO
create database QLBanHang
on
K6_HTTT1_Nhóm 13

Trang 22


Trường ĐH Công Nghiệp Hà Nội

Khoa CNTT

(name=QLBanHang, filename='C:\Program Files\Microsoft SQL
Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\QLBanHang.mdf',
size=50mb, maxsize=200mb, filegrowth=10mb)
log on
(name=QLBanHang_log, filename='C:\Program Files\Microsoft SQL
Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\QLBanHang_Data.mdf',
size=10mb, maxsize=unlimited, filegrowth=5mb)

Bài 2.2: Tạo bảng
USE QLBanHang
GO
create table vattu(
mavtu nvarchar(4) primary key,
tenvtu nvarchar(100),
dvtinh nvarchar(10),
phantram real,

)
create table nhacc(
manhacc nvarchar(3) primary key,
tennhacc nvarchar(100),
diachi nvarchar(200),
dienthoai nvarchar(20),
)
create table dondh(
sodh nvarchar(4),
ngaydh datetime,
manhacc nvarchar(3),
primary key(sodh),
foreign key(manhacc) references nhacc(manhacc)
)
create table ctdondh(
sodh nvarchar(4),
mavtu nvarchar(4),
sldat smallint,
primary key(sodh, mavtu),
foreign key (sodh) references dondh(sodh),
K6_HTTT1_Nhóm 13

Trang 23


Trường ĐH Công Nghiệp Hà Nội

Khoa CNTT

foreign key(mavtu) references vattu(mavtu)

)
create table pnhap(
sopn nvarchar(4),
ngaynhap datetime,
sodh nvarchar(4),
primary key(sopn)
)
create table ctpnhap(
sopn nvarchar(4),
mavtu nvarchar(4),
slnhap smallint,
dgnhap money,
primary key(sopn,mavtu),
foreign key (sopn) references pnhap(sopn),
foreign key(mavtu)references vattu(mavtu)
)
create table pxuat(
sopx nvarchar(4),
ngayxuat datetime,
tenkh nvarchar(100),
primary key(sopx)
)
create table ctpxuat(
sopx nvarchar(4),
mavtu nvarchar(4),
slxuat smallint,
dgxuat money,
primary key(sopx, mavtu),
foreign key(sopx) references pxuat(sopx),
foreign key(mavtu) references vattu(mavtu)

)
create table tonkho(
namthang nvarchar(6),
mavtu nvarchar(4),
sldau smallint,
tongsln smallint,
K6_HTTT1_Nhóm 13

Trang 24


Trường ĐH Công Nghiệp Hà Nội

Khoa CNTT

tongslx smallint,
slcuoi smallint,
primary key(namthang, mavtu),
foreign key(mavtu) references vattu(mavtu)
)
GO

• Các bảng đã có sau khi chạy các câu lệnh:
Bảng vattu

Bảng nhacc

Bảng dondh

Bảng ctdondh


Bảng pnhap

Bảng ctpnhap
K6_HTTT1_Nhóm 13

Trang 25