TRƯỜNG ĐẠI HỌC THƯƠNG MẠI
Khoa: HTTT Kinh Tế & TMĐT

..................

BÀI THẢO LUẬN
Học phần: Quản trị cơ sở dữ liệu
Đề tài: Xây dựng và quản trị cơ sở dữ liệu thị trường
chứng khoán/ngân hàng

Giảng viên hướng dẫn: Nghiêm Thị Lịch
Lớp học phần: 2005ECIT2521
Nhóm trình bày: Nhóm 20

Hà Nội, tháng 04 năm 2020


Mục lục
CHƯƠNG I: MÔ TẢ VÀ XÁC ĐỊNH CÁC CHỨC NĂNG CỦA BÀI TỐN............1
1.1.

Mơ tả bài tốn......................................................................................................1

1.2.

Mơ hình phân cấp chức năng.............................................................................1

1.3.

Q trình thiết kế mơ hình thực thể liên kết (ER)............................................2

1.4.

Mơ hình quan hệ..................................................................................................5

CHƯƠNG 2: CÀI ĐẶT CSDL TRÊN HỆ QUẢN TRỊ CSDL......................................6
2.1.

Tạo Database và các Table.................................................................................6

2.1.1. Tạo Database....................................................................................................6
2.1.2. Tạo bảng khách hàng........................................................................................6
2.1.3. Tạo bảng tài khoản............................................................................................6
2.1.4. Tạo bảng giao dịch............................................................................................6
2.1.5. Tạo bảng loại giao dịch.....................................................................................6
2.2.

Nhập dữ liệu cho các bảng.................................................................................7

2.2.1. Bảng khách hàng...............................................................................................7
2.2.2. Bảng tài khoản..................................................................................................7
2.2.3. Bảng giao dịch..................................................................................................8
2.2.4. Bảng loại giao dịch............................................................................................8
2.3.

Thiết lập các ràng buộc......................................................................................8

CHƯƠNG 3: KHAI THÁC CSDL................................................................................10
3.1.

Query..................................................................................................................10


3.2. View....................................................................................................................12
3.3. Trigger................................................................................................................14
3.4.

Procedure..........................................................................................................16

3.5.

Function.............................................................................................................18

3.6.

Phân quyền........................................................................................................19


CHƯƠNG I: MÔ TẢ VÀ XÁC ĐỊNH CÁC CHỨC NĂNG CỦA BÀI TỐN
1.1.

Mơ tả bài tốn.

Bài tốn quản lý cung cấp dịch vụ Internet banking của ngân hàng.
Một ngân hàng ABC cung cấp dịch vụ internet banking tới khách hàng với các loại giao
dịch khác nhau.
1.2.

Thông tin khách hàng được lưu trữ gồm: Mã khách hàng (duy nhất), họ tên, giới
tính, ngày sinh, địa chỉ, số điện thoại, số căn cước công dân.
Mỗi tài khoản chỉ được sở hữu bởi một khách hàng và một khách hàng có thể tạo
được tối đa 2 tài khoản, khách hàng có thể sử dụng thẻ “tín dụng” hoặc “ghi nợ” để

liên kết với tài khoản cá nhân của mình.
Thơng tin về tài khoản được lưu trữ bao gồm: Mã tài khoản (duy nhất), mã khách
hàng, ngày tạo tài khoản, loại thẻ liên kết và số dư trong tài khoản.
Một tài khoản có thể thực hiện nhiều giao dịch, nhưng một giao dịch chỉ được thực
hiện bởi một tài khoản. Thông tin về giao dịch bao gồm: Mã giao dịch (duy nhất),
mã tài khoản, ngày giao dịch, loại giao dịch, số tiền giao dịch.
Ngoài ra, mỗi một giao dịch sẽ tương ứng với một loại giao dịch, và một loại giao
dịch sẽ được nhiều giao dịch sử dụng.
Mơ hình phân cấp chức năng

1


1.3.

Q trình thiết kế mơ hình thực thể liên kết (ER)

Bước 1: Xác định tập các thực thể.
Thông qua bài toán, tập các thực thể được xác định bao gồm: Khách hàng, Tài khoản,
Giao dịch, Loại giao dịch.
Bước 2: Xác định mối quan hệ giữa các thực thể.

Bước 3: Xác định tập thuộc tính, gắn các thuộc tính cho tập thực thể và mối liên kết.
KHACHHANG(MaKH, HoTen, GioiTinh, NgaySinh, DiaChi, SDT, CCCD)
TAIKHOAN(MaTK, NgayTao, SoDu, LoaiTheLK)
GIAODICH(MaGD, SoTienGD, NgayGD)
2


LOAIGIAODICH(MaLoaiGD, TenLoaiGD)

Bước 4: Xác định miền giá trị cho các thuộc tính.
Thực thể: Khách hàng
Column Name
MaKH
HoTen
GT
NgaySinh
SDT
DiaChi
CCCD

Data Type
char(7)
nvarchar(30)
nvarchar(3)
date
char(10)
nvarchar(60)
char(14)

Allow Null
no
no
no
no
no
no
no

Note

Mã khách hàng
Họ tên
Giới tính
Ngày sinh
Số điện thoại
Địa chỉ
Căn cước cơng dân

Thực thể: Tài khoản
Column Name
MaTK
NgayTao
SoDu
LoaiTheLK

Data Type
char(6)
date
int
nvarchar(20)

Allow Null
no
no
no
no

Note
Mã tài khoản
Ngày tạo

Số dư
Loại thẻ liên kết

Thực thể: Giao dịch
Column Name
MGD
SoTienGD
NgayGD

Data Type
char(8)
int
date

Allow Null
no
yes
no

Note
Mã giao dịch
Số tiền giao dịch
Ngày giao dịch

Thực thể: Loại giao dịch
Column Name
MaLoaiGD
TenLoaiGD

Data Type

char(7)
nvarchar(40)

Allow Null
no
no

Note
Mã loại giao dịch
Tên loại giao dịch

Bước 5: Xác định thuộc tính khóa.
KHACHHANG(MaKH, HoTen, GioiTinh, NgaySinh, DiaChi, SDT, CCCD)
TAIKHOAN(MaTK, NgayTao, SoDu, LoaiTheLK)
GIAODICH(MaGD, SoTienGD, NgayGD)
LOAIGIAODICH(MaLoaiGD, TenLoaiGD)
Bước 6: Xác định bậc tối thiểu và bậc tối đa cho các mối quan hệ.
Một tài khoản chỉ được sở hữu bởi một khách hàng.
3


Một khách hàng có thể sở hữu một hoặc hai tài khoản.

Một giao dịch chỉ được thực hiện từ 1 tài khoản.

Một tài khoản có thể khơng được thực hiện giao dịch nào hoặc được thực hiện nhiều giao
dịch.

Một loại giao dịch có thể khơng được sử dụng trong một giao dịch nào hoặc được sử dụng
trong nhiều giao dịch.


Một giao dịch chỉ thuộc một loại giao dịch.

Bước 7: Vẽ mơ hình ER.

4


1.4.

Mơ hình quan hệ

Dựa vào các quy tắc chuyển đổi từ mơ hình ER sang mơ hình quan hệ, mơ hình ER sau
khi chuyển đổi thành mơ hình quan hệ được thể hiện trong hình dưới:

5


Các thuộc tính của thực thể sau khi chuyển đổi cũng có một số thay đổi như sau:
KHACHHANG(MaKH, HoTen, GioiTinh, NgaySinh, DiaChi, SDT, CCCD)
TAIKHOAN(MaTK, NgayTao, SoDu, LoaiTheLK, MaKH)
GIAODICH(MaGD, SoTienGD, NgayGD, MaTK, MaLoaiGD)
LOAIGIAODICH(MaLoaiGD, TenLoaiGD)
CHƯƠNG 2: CÀI ĐẶT CSDL TRÊN HỆ QUẢN TRỊ CSDL

6


2.1. Tạo Database và các Table.
2.1.1. Tạo Database

create database QLNH;
2.1.2. Tạo bảng khách hàng
use QLNH;
create table KHACHHANG (
MaKH char(7) primary key,
HoTen nvarchar(30) not null,
GT nvarchar(3) not null,
NgaySinh date not null,
SDT char(10) not null,
DiaChi nvarchar(60) not null,
CCCD char(14) not null
);
go
2.1.3. Tạo bảng tài khoản
use QLNH;
create table TAIKHOAN (
MaTK char(7) primary key,
MaKH char(7) not null,
NgayTao date not null,
SoDu int ,
LoaiTheLK nvarchar(11) not null
);
go
2.1.4. Tạo bảng giao dịch
use QLNH;
create table GIAODICH (
MGD char(8) primary key,
MaTK char(7) not null,
MaLoaiGD char(7) not null,
SoTienGD int ,

NgayGD date not null
);
go
2.1.5. Tạo bảng loại giao dịch
use QLNH;
create table LOAIGIAODICH (
MaLoaiGD char(7) primary key,
TenLoaiGD nvarchar(40) not null
);
go
7


2.2. Nhập dữ liệu cho các bảng.
2.2.1. Bảng khách hàng

2.2.2. Bảng tài khoản

2.2.3. Bảng giao dịch

8


2.2.4. Bảng loại giao dịch

2.3. Thiết lập các ràng buộc
 Khóa ngồi liên kết giữa bảng khách hàng và tài khoản
use QLNH;
alter table dbo.TAIKHOAN
add constraint FK_KHACHHANG_TAIKHOAN

foreign key (MaKH) references dbo.KHACHHANG(MaKH);
 Khóa ngồi liên kết giữa bảng tài khoản và bảng giao dịch
9


alter table dbo.GIAODICH
add constraint FK_TAIKHOAN_GIAODICH
foreign key (MaTK) references dbo.TAIKHOAN(MaTK);
 Khóa ngoài liên kết giữa bảng giao dịch và bảng loại giao dịch
use QLNH;
alter table dbo.GIAODICH
add constraint FK_LoaiGD_GIAODICH
foreign key (MaLoaiGD) references dbo.LOAIGIAODICH(MaLoaiGD);

10


CHƯƠNG 3: KHAI THÁC CSDL
3.1. Query
 Đưa ra thông tin về số lần được thực hiện của mỗi loại giao dịch trong năm 2019, sắp
xếp theo thứ tự tăng dần của số lần được thực hiện, nếu trùng nhau thì sắp xếp theo
thứ tự giảm dần của trường tên loại giao dịch.
select LOAIGIAODICH.MaLoaiGD, LOAIGIAODICH.TenLoaiGD,
count(GIAODICH.MGD) as N'số lần được thực hiện'
from LOAIGIAODICH
inner join GIAODICH on LOAIGIAODICH.MaLoaiGD = GIAODICH.MaLoaiGD
where year(GIAODICH.NgayGD) = 2019
group by LOAIGIAODICH.MaLoaiGD, LOAIGIAODICH.TenLoaiGD
order by count(GIAODICH.MGD) asc, LOAIGIAODICH.TenLoaiGD desc
go


 Hiển thị thơng tin khách hàng có tài khoản được liên kết với thẻ “Ghi nợ” theo thứ tự
giảm dần của số dư tài khoản và đếm số giao dịch được thực hiện trên mỗi tài khoản
đó.
use QLNH
select KHACHHANG.MaKH, TAIKHOAN.MaTK, HoTen, SoDu, LoaiTheLK,
count(MGD) as 'Số lần giao dịch'
from TAIKHOAN
inner join KHACHHANG on TAIKHOAN.MaKH = KHACHHANG.MaKH
inner join GIAODICH on TAIKHOAN.MaTK = GIAODICH.MaTK
where LoaiTheLK =N'Ghi nợ'
group by KHACHHANG.MaKH, HoTen, LoaiTheLK, SoDu, TAIKHOAN.MaTK
order by SoDu desc
11


 Đưa ra thông tin giao dịch được thực hiện gần đây nhất với số tiền giao dịch từ 20
triệu trở nên. Thông tin đưa ra bao gồm: mã giao dich, mã tài khoản, tên loại giao
dịch, số tiền giao dịch, ngày giao dịch.
use QLNH
select top 1 percent GIAODICH.MGD, GIAODICH.MaTK,
LOAIGIAODICH.TenLoaiGD, GIAODICH.SoTienGD, GIAODICH.NgayGD
from dbo.GIAODICH
inner join dbo.LOAIGIAODICH on dbo.GIAODICH.MaLoaiGD =
dbo.LOAIGIAODICH.MaLoaiGD
where dbo.GIAODICH.SoTienGD > 20000000
order by GIAODICH.NgayGD desc

12



3.2. View
-

Tạo View chứa thông tin các tài khoản chưa thực hiện một giao dịch nào.

create view Vtkgd
as
select dbo.TAIKHOAN.MaTK, TAIKHOAN.MaKH, TAIKHOAN.NgayTao,
TAIKHOAN.SoDu, TAIKHOAN.LoaiTheLK
from dbo.TAIKHOAN
left join dbo.GIAODICH on dbo.TAIKHOAN.MaTK = dbo.GIAODICH.MaTK
where dbo.GIAODICH.MGD is null
go

 Tạo view chứa thông tin các khách hàng ở thành phố Hồ Chí Minh tạo tài khoản trong
năm 2018. Thông tin bao gồm: mã khách hàng, tên khách hàng, giới tính, sđt, địa chỉ,
mã tài khoản, ngày tạo tài khoản.
use QLNH
create view v_KH
as
select KHACHHANG.MaKH, HoTen, GT, DiaChi, TAIKHOAN.MaTK,
TAIKHOAN.NgayTao
from dbo.KHACHHANG
inner join dbo.TAIKHOAN on dbo.KHACHHANG.MaKH =
dbo.TAIKHOAN.MaKH
where dbo.KHACHHANG.DiaChi = N'Thành phố Hồ Chí Minh' and
year(TAIKHOAN.NgayTao) = 2018
go


13


 Tạo view chứa thông tin về số lần thực hiện giao dịch của mỗi tài khoản. Thông tin
đưa ra bao gồm: Mã KH, tên KH, mã TK, ngày tạo, số dư, số lần thực hiện giao dịch.
use QLNH
create view v_kh_tk_gd
as
select KHACHHANG.MaKH, HoTen, TAIKHOAN.MaTK, NgayTao, SoDu,
count(GIAODICH.MGD) as N'số lần giao dịch'
from dbo.TAIKHOAN
inner join dbo.KHACHHANG on dbo.TAIKHOAN.MaKH =
dbo.KHACHHANG.MaKH
inner join dbo.GIAODICH on dbo.TAIKHOAN.MaTK = dbo.GIAODICH.MaTK
group by TAIKHOAN.MaTK, KHACHHANG.MaKH, HoTen, NgayTao, SoDu
go

14


3.3. Trigger
 Tạo trigger hiển thị thông tin khách hàng sau khi thêm, xóa hoặc sửa dữ liệu
use QLNH
create trigger tr_KH_up_in_de
on dbo.KHACHHANG
for insert, delete, update
as
select * from dbo.KHACHHANG
go


15




Khi tạo mới tài khoản, số dư trong tài khoản phải lớn hơn 50.000 đồng

use QLNH
CREATE TRIGGER sodu
ON dbo.TAIKHOAN
AFTER INSERT
AS
IF UPDATE (SoDu)
BEGIN
DECLARE @tk nchar(10)
SET @tk = (SELECT MaTK FROM inserted)
DECLARE @tien money
SET @tien = (SELECT SoDu from dbo.TAIKHOAN where
dbo.TAIKHOAN.MaTK = @tk)
IF (@tien<50000)
BEGIN
PRINT (N'Số dư tài khoản phải trên 50 nghìn đồng!')
ROLLBACK TRAN
RETURN
END
END

16



3.4. Procedure
 Tạo procedure để thêm dữ liệu vào bảng GIAODICH với các tham số là các dữ liệu
cần thêm vào các cột
use QLNH
create procedure sp_themgd(@magd char(8), @matk char(7), @maloaigd char(7),
@sotiengd int, @ngaygd date )
as
insert into dbo.GIAODICH
values (@magd, @matk, @maloaigd, @sotiengd, @ngaygd)
go

Tạo Store Procedure để lấy ra thông tin khách hàng theo mã khách hàng (MaKH) do
người dùng nhập vào.
create procedure manhap
@MKhachHang char(7)
as
select *
from dbo.KHACHHANG
where dbo.KHACHHANG.MaKH = @MKhachHang
go

17


Tạo Store Procedure lấy ra danh sách giao dịch có mã loại giao dịch (MaLoaiGD) do
người dùng nhập vào và ngày giao dịch (NgayGD) được thực hiện trước ngày do
người dùng nhập vào với các thông tin: Họ tên khách hàng, mã tài khoản, mã giao
dịch, số tiền giao dịch, ngày giao dịch.
create procedure nhapma_ngay
@MaLGD char(7),

@NgayGD date
as
select KHACHHANG.HoTen, TAIKHOAN.MaTK, GIAODICH.MGD,
GIAODICH.SoTienGD, GIAODICH.NgayGD
from dbo.TAIKHOAN
inner join KHACHHANG on TAIKHOAN.MaKH = KHACHHANG.MaKH
inner join GIAODICH on TAIKHOAN.MaTK = GIAODICH.MaTK
where GIAODICH.MaLoaiGD = @MaLGD and GIAODICH.NgayGD <
@NgayGD
go

18


3.5. Function
Tạo function cho biết mã loại giao dịch, tên loại giao dịch và số giao dịch ứng với mỗi
loại giao dịch.
use QLNH
create function Fn_demsogd()
returns table return
select LOAIGIAODICH.MaLoaiGD, LOAIGIAODICH.TenLoaiGD,
count(GIAODICH.MGD) as N'số giao dịch'
from dbo.LOAIGIAODICH
inner join dbo.GIAODICH on dbo.LOAIGIAODICH.MaLoaiGD =
dbo.GIAODICH.MaLoaiGD
group by LOAIGIAODICH.MaLoaiGD, LOAIGIAODICH.TenLoaiGD
go

Tạo function hiển thị mã khách hàng, tên khách hàng, mã tài khoản, mã giao dịch, mã
loại giao dịch, ngày giao dịch, số tiền giao dịch với mã giao dịch là tham số.

create function Fn_hienthitt(@magd char(8))
returns table return
select KHACHHANG.MaKH, KHACHHANG.HoTen, TAIKHOAN.MaTK,
GIAODICH.MGD, GIAODICH.MaLoaiGD, GIAODICH.NgayGD,
GIAODICH.SoTienGD
from dbo.TAIKHOAN
inner join dbo.KHACHHANG on dbo.TAIKHOAN.MaKH =
dbo.KHACHHANG.MaKH
inner join dbo.GIAODICH on dbo.TAIKHOAN.MaTK = dbo.GIAODICH.MaTK
where dbo.GIAODICH.MGD = @magd
go

19


3.6. Phân quyền
-- Tạo tài khoản đăng nhập và user:
Create login PCT With password = '1234',
DEFAULT_DATABASE = QLNH
Create user abc
for login PCT
--create login nguoiquanly
With password= '5678'
Create user jack
For login nguoiquanly
--Phân quyền cho người dùng abc có thể xem thơng tin của khách hàng:
use QLNH
go
grant select on KHACHHANG to abc
--Phân quyền cho người dùng jack có thể xem, sửa thơng tin khách hàng:

use QLNH
go
grant select, insert, update on KHACHHANG to jack
--Thu hồi quyền người dùng abc, jack:
revoke select on KHACHHANG to abc
revoke select, insert, update on KHACHHANG to jack

20