Ngôn ngữ truy vấn dữ liệu

Giảng viên: Nguyễn Thị Tâm

Phân công công việc

Thành viên
1.member1

Viết truy vấn các dữ liệu :

-Đặt ra các yêu cầu xóa dữ liệu có điều kiện và viết
truy vấn thực hiện
-Tạo các View tương ứng với truy vấn SELECT đã viết
-Viết các stored procedure lấy dữ liệu từ mỗi bảng theo
PK ứng với
Parameter truyền vào
-Viết các stored procedure lấy dữ liệu từ mỗi bảng theo
FK ứng với
Parameter truyền vào
-Viết các stored procedure lấy dữ liệu từ mỗi bảng theo
CK ứng với
Parameter truyền vào

2.member2

Viết truy vấn các dữ liệu :
-Cập nhật thông tin cho từng bảng truy vấn
-Thêm trường, xóa trường
-Đặt ra các yêu cầu lấy dữ liệu (từ 1 và nhiều
bảng; có kết hợp các toán

tử đã học trong các biểu thức điều kiện, biểu thức
tính toán; có sử dụng các
hàm tổng hợp dữ liệu; dùng đủ câc mệnh đề của
truy vấn SELECT). Viết truy
vấn thực hiện từng yêu cầu
-Truy vấn dữ liệu từ 1 bảng
-Truy vấn dữ liệu từ nhiều bảng

3.member3

Viết truy vấn các dữ liệu :
-Phát biểu bài toán
-Phân tích, thiết kế cơ sở dữ liệu
-Tạo bảng truy vấn
-Nhập dữ liệu
-Tạo khoá chính khoá ngoại , ràng buộc

1


Ngôn ngữ truy vấn dữ liệu

Giảng viên: Nguyễn Thị Tâm

Mục Lục
I. Đề tài đã chọn...............................................................................3
II.

Phát biểu bài toán......................................................................3


III. Thiết kế CSDL mức khái niệm-Mô hình liên kết thực thể.4
1. Xác định các thực thể và thuộc tính............................................4
2. Mô hình ER..................................................................................5
3. Thiết kế CSDL mức logic-mô hình quan hệ..............................6
4. Thiết kế vật lý.............................................................................7
5. Code chương trình.......................................................................7

2


Ngôn ngữ truy vấn dữ liệu

Giảng viên: Nguyễn Thị Tâm

BÀI TOÁN QUẢN LÝ CẤP THẺ XE BUS

I.

Đề tài đã chọn

Xây dựng Database và khai thác cơ sở dữ liệu cho bài toán quản lý cấp thẻ xe
bus

II.

Phát biểu bài toán

Bài toán quản lý cấp thẻ xe bus sẽ bao gồm :
Khách hàng đến mua vé xe bus sẽ cần có những thông tin cụ thể
- Khách hàng: bao gồm mã khách hang ,tên khách hang ,ngày sinh ,địa

chỉ ,giới tính ,số điện thoại.
khi đến nơi cấp vé thì sẽ có nhân viên bán vé tháng có những thông tin sau
- Nhân viên:mã nhân viên,tên nhân viên,địa chỉ,giới tính,số điện thoại.
Nhân viên sẽ xuất phiếu thu tiền sau khi nhận tiền của khách hang
- Phiếu thu tiền:mã phiếu , ngày lập , tổng tiền
Vé tháng thì có nhiều loại vé khác nhau sẽ gồm thông tin cụ thể của loại vé
- Loại vé: mã loại vé , loại vé
Mỗi loại vé thì sẽ có 1 mệnh giá tương ứng với vé đó
- Gía vé: mã giá, đơn giá
Khách hàng nhận vé tháng,vé tháng sẽ bao gồm các thông tin
- Vé tháng: mã vé ,ngày bắt đầu , ngày kết thúc

3


Ngôn ngữ truy vấn dữ liệu

III.

Giảng viên: Nguyễn Thị Tâm

Thiết kế CSDL mức khái niệm-Mô hình liên kết thực thể

1. Xác định các thực thể và thuộc tính
1.KHACHHANG (MaKH, TenKH, ngay sinh, dia chi, gioi tinh, SDT)
2.NHANVIEN ( MaNV, TenNV, ngay sinh, dia chi, gioi tinh, SDT)
3.VETHANG (MaV, ngayBD, ngayKT)
4. PHIEUTHU(MaP, ngaythu, TTien)
5.LOAIVE(MaLV ,TenLV)
6.GIAVE(MaG, dongia)

Biểu diễn các mối lien kết:







KHACHHANG mua VE THANG:1-n
NHANVIEN lập PHIEUTHU:1-n
KHACHHANG nhận PHIEUTHU:1-n
PHIEUTHU có VETHANG : 1-1
VETHANG có LOAIVE:1-1
LOAIVE có GIAVE:1-1

4


Ngôn ngữ truy vấn dữ liệu

Giảng viên: Nguyễn Thị Tâm

2. Mô hình ER

5


Ngôn ngữ truy vấn dữ liệu

Giảng viên: Nguyễn Thị Tâm


3. Thiết kế CSDL mức logic-mô hình quan hệ
Áp dụng thực thể mạnh:
(1) KHACHHANG (MaKH ,TenKH ,ngay sinh ,dia chi ,gioi tinh
,SDT)
(2) NHANVIEN ( MaNV ,TenNV ,ngay sinh ,dia chi ,gioi tinh ,SDT)
(3) VETHANG (MaV ,ngayBD ,ngayKT)
(4) PHIEUTHU(MaP ,ngaythu , TTien)
(5) LOAIVE(MaLV , TenLV)
(6) GIAVE(MaG ,dongia)
Áp dụng với quan hệ 1-n:
(3’) VETHANG (MaV ,MaKH ,ngayBD ,ngayKT)
(4’) PHIEUTHU(MaP, MaNV ,ngaythu, TTien)
(4’’) PHIEUTHU(MaP, MaNV , MaKH ,ngaythu , TTien)
Áp dụng quan hệ 1-1:
(3’’)VETHANG (MaV , MaLV, MaKH, ngayBD, ngayKT)
(4’’’) PHIEUTHU(MaP ,MaV , MaNV , MaKH ,ngaythu , TTien)
(6’) GIAVE(MaG ,dongia , MaLV)
CDSL cuối cùng:
(1) KHACHHANG (MaKH , TenKH, ngay sinh, dia chi, gioi tinh
,SDT)
(2)NHANVIEN
,SDT)

(MaNV, TenNV, ngay sinh, dia chi, gioi tinh

(3’’)VETHANG (MaV , MaLV, MaKH, ngayBD, ngayKT)
(4’’’) PHIEUTHU(MaP ,MaV , MaNV , MaKH ,ngaythu , TTien)

6



Ngôn ngữ truy vấn dữ liệu

Giảng viên: Nguyễn Thị Tâm

(5) LOAIVE(MaLV , TenLV)
(6’) GIAVE(MaG ,dongia , MaLV)

4. Thiết kế vật lý

5. Code chương trình
create database BTL
use BTL
drop database BTL
---tạo bảng Người mua vé
create table KHACHHANG
(
sMakh varchar(10) primary key,
sTenkh nvarchar(30),
sNgaysinh date,
sDiachi nvarchar(30),
sGtinh varchar(5),
sSdt varchar(20),
);

7


Ngôn ngữ truy vấn dữ liệu


Giảng viên: Nguyễn Thị Tâm

---tạo bảng nhân viên phát vé
create table NHANVIEN
(
sManv varchar(10) primary key,
sTennv nvarchar(30),
sNgaysinh date,
sDiachi nvarchar(30),
sGtinh varchar(5),
sSdt varchar(30),
);
---tạo bảng loại vé tháng
create table LOAIVE
(
sMaloaive varchar(10) primary key,
sLoaive varchar(10),
);
---tạo đơn giá từng loại vé tháng
create table GIAVE
(
sMagiave varchar(10) primary key,
fDongia float,
sMaloaive varchar(10),
foreign key (sMaloaive) references LOAIVE(sMaloaive),
);
---tạo bảng vé tháng
create table VETHANG
(

sMave varchar(10) primary key,
sNgayketthuc date,
sMakh varchar(10),
sMaloaive varchar(10),
sNgaybatdau date,
foreign key (sMaloaive) references LOAIVE(sMaloaive),
foreign key (sMakh)
references KHACHHANG(sMakh),
);

8


Ngôn ngữ truy vấn dữ liệu

Giảng viên: Nguyễn Thị Tâm

---tạo bảng phiếu thu tiền
create table PHIEUTHUTIEN
(
sMaphieu varchar(10) primary key,
sNgaylap date,
sMave varchar(10),
sManv varchar(10),
sMakh varchar(10),
sTongtien float,
foreign key (sMave) references VETHANG(sMave),
foreign key (sManv) references NHANVIEN(sManv),
foreign key (sMakh) references KHACHHANG(sMakh),
);

--Tạo các index
create index id_TTkhach on KHACHHANG(sMaKH);
create index id_TTGia on GIAVE(sMaloaive);
create index id_TTLV on LOAIVE(sMaloaive);
create index id_TTPhieu on PHIEUTHUTIEN(sMave);
create index id_TTVe on VETHANG(sNgaybatdau);
create index id_TTNvien on NHANVIEN(sManv);
--Tạo rang buoc
alter table KHACHHANG
add constraint CK_GT CHECK (sGtinh in('Nam','Nu'));
alter table NHANVIEN
add constraint GT_NV CHECK (sGtinh in('Nam','Nu'));
alter table NHANVIEN drop GT_NV
--Xóa trường thứ 2 ở mỗi bảng
alter table KHACHHANG drop column sTenkh;
alter table NHANVIEN drop column sTennv;
alter table LOAIVE drop column sLoaive;
alter table GIAVE drop column fDongia;
alter table VETHANG drop column sNgayketthuc;
alter table PHIEUTHUTIEN drop column sNgaylap;

--Thêm trường vừa xóa vào mỗi bảng
Alter table KHACHHANG add sTenkh nvarchar(30);
9


Ngôn ngữ truy vấn dữ liệu

Giảng viên: Nguyễn Thị Tâm


Alter table NHANVIEN add sTennv nvarchar(30);
Alter table LOAIVE add sLoaive varchar(10);
Alter table GIAVE add fDongia float;
Alter table VETHANG add sNgayketthuc date;
Alter table PHIEUTHUTIEN add sNgaylap date;
---Nhập dữ liệu cho các bảng
--Bảng khách hàng
Insert into KHACHHANG values
('KH01','09/06/1986',N'Hà Nội','Nu','0123456789',N'Nguyễn Thị Hà');
Insert into KHACHHANG values
('KH02','09/06/1996',N'Hà Nam','Nu','0125498696',N'Đinh Thu
Trâm');
Insert into KHACHHANG values
('KH03','09/06/1995',N'Hải Dương','Nam','0786786782',N'Trần Văn
Long');
Insert into KHACHHANG values
('KH04','09/06/1994',N'Nam Định','Nam','0846521869',N'Phạm Hồng
Tiến');
Insert into KHACHHANG values
('KH05','09/06/1996',N'Hà Nội','Nu','0648566215',N'vũ Thu Trang');
Insert into KHACHHANG values
('KH06','09/06/1992',N'Hà Nội','Nam','0157956346',N'Mai Tiến Sơn');
select * from KHACHHANG

--Bảng Nhân viên
Insert into NHANVIEN values
('NV01','09/06/1988',N'Hà Nội','Nu','0987654321',N'Nguyễn Thị
Thanh Hà');
Insert into NHANVIEN values
('NV02','09/06/1988',N'lạng Sơn','Nu','0946658861',N'Trần Thu Hà');

Insert into NHANVIEN values
('NV03','09/06/1998',N'Gia Lai','Nam','0984656564',N'Nguyễn Văn
Quyết');
Insert into NHANVIEN values
10


Ngôn ngữ truy vấn dữ liệu

Giảng viên: Nguyễn Thị Tâm

('NV04','09/06/1988',N'Hà Nam','Nu','0179455753',N'Trần Thị Bưởi');
Insert into NHANVIEN values
('NV05','09/06/1988',N'Bến Tre','Nam','0618486966',N'Nguyễn Thị
Thanh Tâm');
Insert into NHANVIEN values
('NV06','09/06/1993',N'Hồ Chí Minh','Nu','0123584866',N'Nguyễn
Trà My');
select * from NHANVIEN

--Bảng Loại vé
Insert into LOAIVE values('M01','L1');
Insert into LOAIVE values('M02','L2');
Insert into LOAIVE values('M03','L3');
Insert into LOAIVE values('M04','L4');
Insert into LOAIVE values('M05','L5');
Insert into LOAIVE values('M06','L6');
select * from LOAIVE

--Bảng giá từng loại vé

Insert into GIAVE values('MM01','M01','30000');
Insert into GIAVE values('MM02','M02','40000');
Insert into GIAVE values('MM03','M03','50000');
Insert into GIAVE values('MM04','M04','60000');
Insert into GIAVE values('MM05','M05','70000');
select * from GIAVE

11


Ngôn ngữ truy vấn dữ liệu

Giảng viên: Nguyễn Thị Tâm

--Bảng vé tháng
Insert into VETHANG
values('MV01','KH01','M02','01/01/2017','01/02/2017');
Insert into VETHANG
values('MV02','KH02','M03','01/03/2017','01/04/2017');
Insert into VETHANG
values('MV03','KH03','M05','01/05/2017','01/06/2017');
Insert into VETHANG
values('MV04','KH04','M06','01/07/2017','01/08/2017');
Insert into VETHANG
values('MV05','KH05','M01','01/08/2017','01/09/2017');
Insert into VETHANG
values('MV06','KH06','M04','01/09/2017','01/10/2017');
select * from VETHANG

--Bảng phiếu thu tiền

Insert into PHIEUTHUTIEN
values('MP01','MV01','NV01','KH01','50000','01/02/2017');
Insert into PHIEUTHUTIEN
values('MP02','MV02','NV02','KH02','60000','02/03/2017');
Insert into PHIEUTHUTIEN
values('MP03','MV03','NV03','KH03','60000','03/04/2017');
Insert into PHIEUTHUTIEN
values('MP04','MV04','NV04','KH04','70000','04/05/2017');
Insert into PHIEUTHUTIEN
values('MP05','MV05','NV05','KH05','80000','05/06/2017');
12


Ngôn ngữ truy vấn dữ liệu

Giảng viên: Nguyễn Thị Tâm

Insert into PHIEUTHUTIEN
values('MP06','MV06','NV06','KH06','90000','06/07/2017');
select * from PHIEUTHUTIEN

--Cập nhật lại giá cho vé
update GIAVE
set fDongia ='90000'
where sMaloaive = 'M06'
select *from GIAVE

--Cập nhật lại sđt của khách hàng
update KHACHHANG
set sSdt='0155626648'

where sMakh='KH01'
select *from KHACHHANG

--Cập nhật lại giới tính cho nhân viên
update NHANVIEN
set sGtinh='Nu'
13


Ngôn ngữ truy vấn dữ liệu

Giảng viên: Nguyễn Thị Tâm

where sManv='NV05'
select *from NHANVIEN

--Xoá nhân viên có mã
delete from NHANVIEN
where sManv='NV07'
select * from NHANVIEN

--viết thủ tục thêm khách hàng với các tham số truyền vào phù hợp
create procedure KHACHHANG_Themkh
@Makh varchar(10),
@Ngaysinh date,
@Điachi nvarchar(30),
@Gtinh nvarchar(5),
@Sdt varchar(20),
@Tenkh nvarchar(30)
as

begin
insert into KHACHHANG values
(@Makh,@Ngaysinh,@Điachi,@Gtinh,@Sdt,@Tenkh)
end
exec KHACHHANG_Themkh
@Makh='KH15',@Tenkh=N'Đặng Ngọc Long',
@Ngaysinh='01/04/1996', @Điachi=N'Hà Nội' ,
@Gtinh=N'Nam', @Sdt='0649496666'
select *from KHACHHANG
14


Ngôn ngữ truy vấn dữ liệu

Giảng viên: Nguyễn Thị Tâm

---TRUY VẤN dữ liệu từ một bảng
--Tìm khách hàng có năm sinh
select *from KHACHHANG
where year(sNgaysinh) = 1996

--Thông tin nhân viên có chữ cái đầu tiên là N
select *from NHANVIEN where sTennv like 'N%'

--Tìm phiếu thu tiền có ngày lập là 06/07/2017
select *from PHIEUTHUTIEN
where sNgaylap='06/07/2017'

--Số khách hàng có gtính là nam
select count(sMakh) as SoKH

from KHACHHANG
where sGtinh='nam'

15


Ngôn ngữ truy vấn dữ liệu

Giảng viên: Nguyễn Thị Tâm

---TRUY VẤN dữ liệu từ nhiều bảng
--Danh sách nhân viên và ngày lập phiếu
select NHANVIEN.sManv,PHIEUTHUTIEN.sNgaylap
from NHANVIEN inner join PHIEUTHUTIEN
on NHANVIEN.sManv=PHIEUTHUTIEN.sManv

--Danh sách Mã loại vé tương ứng mã khách hàng
select LOAIVE.sMaloaive,VETHANG.sMakh
from LOAIVE inner join VETHANG
on LOAIVE.sMaloaive=VETHANG.sMaloaive

--Danh sách Mã loại vé tương ứng đơn giá
select LOAIVe.sMaloaive,GIAVE.fDongia
from LOAIVE inner join GIAVE
on LOAIVE.sMaloaive=GIAVE.sMaloaive

---Tạo view tương ứng với select đã có
--Select từ 1 bảng
--Tạo view gồm ( tên khách hàng và năm sinh )
create view view_ngsinh

16


Ngôn ngữ truy vấn dữ liệu

Giảng viên: Nguyễn Thị Tâm

as
select sTenkh,sNgaysinh
from KHACHHANG
where year(sNgaysinh) = 1996
select *from view_ngsinh

--Tạo view gồm ( mã nhân viên và tên nhân viên )
create view view_ttin
as
select sManv,sTennv
from NHANVIEN
where sTennv like 'N%'
select *from view_ttin

--Tạo view gồm ( phiếu thu tiền được lập ngày 06/07/2017)
create view view_ngay
as
select sMaphieu,sNgaylap
from PHIEUTHUTIEN
where sNgaylap='06/07/2017'
select *from view_ngay

--Tạo view gồm ( số nhân viên là nam )

create view view_soKh
as
select count(sMakh) as SoKH
from KHACHHANG
where sGtinh='nam'
select *from view_soKh

17


Ngôn ngữ truy vấn dữ liệu

Giảng viên: Nguyễn Thị Tâm

--Select từ nhiều bảng
--Tạo view gồm mã nhân viên và ngày lập phiếu thu tiền
create view view_tt1
as
select NHANVIEN.sManv,PHIEUTHUTIEN.sNgaylap
from NHANVIEN inner join PHIEUTHUTIEN
on NHANVIEN.sManv=PHIEUTHUTIEN.sManv
select *from view_tt1

--Tạo view gồm mã loại vé và mã khách hàng
create view view_tt2
as
select LOAIVE.sMaloaive,VETHANG.sMakh
from LOAIVE inner join VETHANG
on LOAIVE.sMaloaive=VETHANG.sMaloaive
select *from view_tt2


--Tạo view gồm mã loại vé và đơn giá
create view view_tt3
as
select LOAIVe.sMaloaive,GIAVE.fDongia
from LOAIVE inner join GIAVE
on LOAIVE.sMaloaive=GIAVE.sMaloaive
select *from view_tt3

18


Ngôn ngữ truy vấn dữ liệu

Giảng viên: Nguyễn Thị Tâm

---Tạo procedure lấy dữ liệu theo PK
--Bảng khách hàng
create proc sp_pk_khachhang
(
@sMakh varchar(50)
)
as
begin
select * from KHACHHANG
where sMakh=@sMakh
end
exec sp_pk_khachhang 'KH01';
select *from KHACHHANG
--Bảng nhân viên

create proc sp_pk_nhanvien
(
@sManv varchar(50)
)
as
begin
select * from NHANVIEN
where sManv=@sManv
end
exec sp_pk_nhanvien 'NV01';
--Bảng loại vé
create proc sp_pk_loaive
(
@sMaloaive varchar(50)
)
as
begin
select * from LOAIVE
19


Ngôn ngữ truy vấn dữ liệu

Giảng viên: Nguyễn Thị Tâm

where sMaloaive=@sMaloaive
end
exec sp_pk_loaive 'M01';
--Bảng Giá vé
create proc sp_pk_giave

(
@sMagiave varchar(50)
)
as
begin
select *from GIAVE
where sMagiave=@sMagiave
end
exec sp_pk_giave 'MM01'
--Bảng Vé tháng
create proc sp_pk_vethang
(
@sMave varchar(50)
)
as
begin
select *from VETHANG
end
exec sp_pk_vethang 'MV01'
--Bảng Phiếu thu tiền
create proc sp_pk_phieuthu
(
@sMaphieu varchar(50)
)
as
begin
select *from PHIEUTHUTIEN
end
exec sp_pk_phieuthu 'MP01'
---Tạo procedure lấy dữ liệu theo FK

--bảng giá vé
create proc sp_fk_gv
(
@sMaloaive varchar(20)
20


Ngôn ngữ truy vấn dữ liệu

Giảng viên: Nguyễn Thị Tâm

)
as
begin
select * from GIAVE
where sMaloaive=@sMaloaive
end
exec sp_fk_gv 'M04'
--bảng vé tháng
create proc sp_fk_vt1
(
@sMaloaive varchar(20)
)
as
begin
select * from VETHANG
where sMaloaive=@sMaloaive
end
exec sp_fk_vt1 'M02'
-------------------------create proc sp_fk_vt2

(
@sMakh varchar(20)
)
as
begin
select * from VETHANG
where sMakh=@sMakh
end
exec sp_fk_vt2 'KH02'
--bảng phiếu thu
create proc sp_fk_pt1
(
@sMakh varchar(20)
)
as
begin
select * from PHIEUTHUTIEN
where sMakh=@sMakh
end
exec sp_fk_pt1 'KH06'
---------------------------21


Ngôn ngữ truy vấn dữ liệu

Giảng viên: Nguyễn Thị Tâm

create proc sp_fk_pt2
(
@sManv varchar(10)

)
as
begin
select * from PHIEUTHUTIEN
where sManv=@sManv
end
exec sp_fk_pt2 'NV06'
---------------------------create proc sp_fk_pt3
(
@sMave varchar(10)
)
as
begin
select * from PHIEUTHUTIEN
where sMave=@sMave
end
exec sp_fk_pt3 'MV06'
---Tạo procedure lấy dữ liệu theo CK
create proc sp_ck_gt
(
@sGtinh varchar(5)
)
as
begin
select *from KHACHHANG
where sGtinh=@sGtinh
end
exec sp_ck_gt 'nam';

22