Bài tập thực hành

CSDL – TH107

CƠ SỞ DỮ LIỆU
QUẢN LÝ ĐỊA ỐC
Công ty tư vấn dịch vụ ñịa ốc Anh Quân muốn tin học hóa nghiệp vụ Dịch vụ cho người bán tại chợ ñịa ốc A&Q của mình.
Sau ñây là một phần CSDL của hệ thống:
1. NGUOI_BAN(MANB, HoTen, DiaChi, SoDT).
Tân từ: Người bán là người ñến công ty xin thực hiện quảng cáo cho ñịa ốc của họ. Mỗi người bán có một mã ñể phân biệt
với các người bán khác và có những thông tin như họ tên, ñịa chỉ liên hệ và số ñiện thoại liên lạc.
2. DIA_OC(MaDO, So, Duong, Phuong, Quan, DTDat, DTXD, Huong, ViTri, MoTa, MaLDO).
Tân từ: Mỗi ñịa ốc cần bán gồm các thông tin sau: mã ñể phân biệt, mã loại ñịa ốc, ñịa chỉ ñịa ốc cần bán (số nhà, ñường
phố, phường và quận), diện tích ñất, diện tích xây dựng tính bằng m2, hướng nhà ñất, vị trí, mô tả chi tiết, mã loại ñịa ốc.
3. LOAI_DIA_OC(MaLDO, TenLDO)
Tân từ: mỗi loại ñịa ốc có một mã ñể phân biệt, tên của lọai ñịa ốc ñó.
4. DICH_VU(MaDV, TenDV, TienDV)
Tân từ: mỗi dịch vụ có một mã ñể phân biệt, ngoài ra còn có tên, và số tiền thuê dịch vụ ñó/tháng.
5. PHIEU_DANG_KY(MaPDK, NgayDK, TongSoDV, TongTien, MaNB).

Bộ Môn HTTT – Khoa CNTT – ĐH KHTN TPHCM

-1-


Bài tập thực hành

CSDL – TH107

Tân từ: Người bán có thể ñăng ký quảng cáo bán ñịa ốc. Mỗi lần ñăng ký sẽ ñược lập một phiếu ñăng ký gồm: mã phiếu
ñăng ký, ngày ñăng ký, tổng số loại dịch vụ, tổng số tiền, mã người bán ñăng ký

6. CT_PDK(MaPDK, MaDO, MaDV, TuNgay, DenNgay, SoTien).
Tân từ: Một phiếu ñăng ký có thể ñăng ký cho nhiều ñịa ốc, hoặc nhiều dịch vụ. Mỗi chi tiết phiếu ñăng ký gồm:mã phiếu
ñăng ký, mã ñịa ốc, mã dịch vụ, thời gian ñăng quảng cáo (TuNgay, DenNgay, ñơn vị tính là tháng), số tiền tính tổng cộng
cho từng dịch vụ tương ứng với số tháng người bán chọn ñăng ký quảng cáo.
7. PHIEU_THU(MaPT, PhieuDK, NgayThu, LanThu, SoTien, MaPTGoc).
Tân từ Khi lập phiếu ñăng ký lần ñầu tiên thì tương ứng sẽ lập một phiếu thu gốc cho phiếu ñăng ký ñó. Sau khi phiếu ñăng
ký hết hạn, người bán có nhu cầu gia hạn thời gian quảng cáo thì nhân viên sẽ lập phiếu gia hạn. , và tương ứng nhân viên
cũng sẽ lập phiếu thu mới cho lần gia hạn ñó dựa trên phiếu thu gốc ban ñầu. Tuy nhiên, người bán ñịa ốc có thể thanh
toán tiền quảng cáo cho lần ñăng ký ñầu tiên cũng như những lần gia hạn sau ñó thành nhiều ñợt. Mỗi ñợt sẽ ñược lập một
phiếu thu. Trong lần thu ñầu tiên của phiếu ñăng ký hoặc gia hạn, người ñăng ký phải thanh toán ít nhất 50% tổng số tiền
của lần ñăng ký hoặc gia hạn ñó, phần còn lại có thể thanh toán thành nhiều lần miễn là thanh toán xong trước khi việc
ñăng ký hoặc gia hạn hết hiệu lực. Thông tin của phiếu thu gồm có: mã phiếu thu, mã phiếu ñăng ký, ngày thu, lần thu, số
tiền, mã phiếu thu gốc. Nếu là lần thu ñầu tiên thì mã phiếu thu gốc là NULL, mã phiếu ñăng ký là NOT NULL. Nếu phiếu
thu là những lần thu cho các lần gia hạn, thì mã phiếu thu gốc chính là mã phiếu thu ñầu tiên thu cho ñăng ký ñó và mã
phiếu ñăng ký là NULL.
8. PHIEU_GIA_HAN(MaPGH, PhieuDK, MaDO, MaDV, NgayGiaHan, TuNgay, DenNgay)

Bộ Môn HTTT – Khoa CNTT – ĐH KHTN TPHCM

-2-


Bài tập thực hành

CSDL – TH107

Tân từ: Nếu phiếu ñăng ký ñã hết hạn thực hiện quảng cáo thì người bán có thể xin gia hạn. Người bán có thể yêu cầu xin
gia hạn một hoặc nhiều dịch vụ có trong lần ñăng ký ñầu tiên mà họ có nhu cầu, không nhất thiết phải gia hạn toàn bộ các
dịch vụ có trong lần ñăng ký ñầu tiên. Mỗi lần gia hạn cho lần ñăng ký nào ñó nhân viên sẽ lập phiếu gia hạn. Giá của
dịch vụ quảng cáo gia hạn sẽ ñược giảm 20% so với ñơn giá ban ñầu. Thông tin phiếu gia hạn gồm có: mã phiếu gian hạn,

gia hạn cho phiếu ñăng ký nào, mã ñia ốc cần quảng cáo, lọai dịch vụ quảng cáo, ngày lập phiếu gia hạn, thời gian quảng
cáo mới (TuNgay, DenNgay).
Bảng mô tả kiểu dữ liệu cho các thuộc tính
STT
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

Tên cột
MaNB
HoTen
DiaChi
SoDT
MaDO
So
Duong
Phuong

Quan
DTDat
DTXD
Huong
ViTri
MoTa
MaLDO
TenLDO

Kiểu dữ liệu
Char(9)
Varchar(50)
Varchar(70)
Char(10)
Char(9)
Varchar(10)
Varchar(30)
Varchar(30)
Varchar(30)
Real
Real
Varchar(10)
Varchar(20)
Varchar(100)
Int
Varchar(50)

17
18
19

20
21
22
23
24
25
26
27
28
29
30
31

Bộ Môn HTTT – Khoa CNTT – ĐH KHTN TPHCM

MaDV
TenDV
TienDV
MaPDK
NgayDK
TongSoDV
TongTien
TuNgay
DenNgay
SoTien
MaPT
NgayThu
LanThu
MaPGH
NgayGH


Int
Varchar(50)
Money
Char(10)
DateTime
Int
Money
DateTime
DateTime
Money
Char(10)
DateTime
Int
Char(10)
DateTime

-3-


Bài tập thực hành

CSDL – TH107

Dữ liệu minh họa cho các bảng:
NGUOI_BAN
MANB
111222333
222333444
333444555


HOTEN
Nguyễn Tường Vân
Trần Thanh Tùng
Nguyễn Ngọc Nga

CT_PDK

DiaChi
330/2 Lê Hồng Phong Quận 5
111 Trương Định Quận 3
315 An Dương Vương Quận 5

SoDT
8111222
8222333
8333444

MAPDK
PDK111
PDK222
PDK222
PDK333
PDK333
PDK333

MADO
DO111
DO222
DO333

DO444
DO444
DO555

MADV
1
1
2
1
2
3

TuNgay
5/5/2005
1/11/2005
1/11/2005
15/9/2005
15/9/2005
15/9/2005

DenNgay
5/7/2005
31/12/2005
31/12/2005
15/10/2005
15/10/2005
15/10/2005

SOTIEN
400.000

400.000
200.000
200.000
100.000
300.000

PHIEU_GIA_HAN
MAPGH
PGH111
PGH222
PGH444
PGH444

PHIEUDK
PDK111
PDK111
PDK333
PDK333

MaDO
DO111
DO111
DO444
DO555

MaDV
1
1
1
3


NGAYGIAHAN
5/7/2005
1/11/2005
15/11/2005
15/11/2005

TUNGAY
5/7/2005
1/11/2005
15/11/2005
15/11/2005

DENNGAY
5/9/2005
31/12/2005
15/12/2005
15/12/2005

DIA_OC
MADO
DO111
DO222
DO333
DO444
DO555

SO
731
638

332/1
980
111/45

DUONG
Trần Hưng Đạo
Nguyễn Văn Cừ
Nguyễn Thái Học
Lê Hồng Phong
Trương Định

PHUONG
7
5
9
4
10

QUAN
1
5
1
5
3

DTDAT
1000
500
100
450

85

DTXD
800
450
100
450
85

HUONG
Đông
Tây
Nam
Bắc
Đông Nam

VITRI
Mặt tiền
Mặt tiền
Hẻm
Mặt tiền
Hẻm

MOTA

MALDO
1
2
1
2

1

PHIEU_DANG_KY
MAPDK
PDK111
PDK222
PDK333

NGAYDK
1/5/2005
19/10/2005
7/9/2005

TONGSODV
1
2
3

TONGTIEN
1.040.000
600.000
1.000.000

Bộ Môn HTTT – Khoa CNTT – ĐH KHTN TPHCM

MANB
111222333
222333444
333444555


-4-


Bài tập thực hành

CSDL – TH107

PHIEU_THU
MAPT
PT111
PT222
PT333
PT444
PT555
PT666
PT777

PHIEUDK
PDK111
PDK222
NULL
NULL
NULL
PDK333
NULL

NGAYTHU
1/5/2005
19/10/2005
15/12/2005

5/7/2005
1/11/2005
7/9/2005
15/11/2005

LANTHU
1
1
2
2
3
1
2

DICH_VU
SOTIEN
400.000
400.000
200.000
320.000
320.000
600.000
400.000

MAPTGOC
NULL
NULL
PT222
PT111
PT111

NULL
PT666

MADV
1
2
3

TENDV
Tờ bướm quảng cáo 200 tờ
Tờ bướm quảng cáo 100 tờ
Quảng cáo trên báo

TIENDV
200.000
100.000
300.000

LOAI_DIA_OC
MALDO
1
2

TENLDO
Nhà và ñất
Đất

Trả lời các câu hỏi sau bằng ngôn ngữ SQL:
Truy vấn ñơn giản:
1. Cho biết danh sách những người bán có ñăng ký quảng cáo với thời hạn sử dụng từ tháng 8 ñến tháng 10 năm 2006.

Thông tin mỗi người bán cần xuất ra gồm mã người bán, họ tên, ñịa chỉ theo ñúng thứ tự này, các dòng dữ liệu kết
quả không ñược trùng nhau.
2. Cho biết danh sách những người bán có ñăng ký quảng cáo với ngày ñăng ký nằm trong tháng 10 năm 2005.
Thông tin mỗi người bán cần xuất ra gồm mã người bán, họ tên, ñịa chỉ theo ñúng thứ tự này, các dòng dữ liệu kết
quả không ñược trùng nhau.
3. Cho biết danh sách những người bán lập phiếu gia hạn vào tháng 11 năm 2005.
Thông tin mỗi người bán cần xuất ra gồm mã người bán, họ tên, ñịa chỉ theo ñúng thứ tự này, các dòng dữ liệu kết
quả không ñược trùng nhau
4. Cho biết danh sách những người bán ñịa ốc có ñăng ký dịch vụ quảng cáo trên báo.
Thông tin mỗi người bán cần xuất ra gồm mã người bán, họ tên, ñịa chỉ, số ñiện thoại theo ñúng thứ tự này, các
dòng dữ liệu kết quả không ñược trùng nhau.
Truy vấn có sử dụng aggregation:

Bộ Môn HTTT – Khoa CNTT – ĐH KHTN TPHCM

-5-


Bài tập thực hành

CSDL – TH107

5. Với mỗi người bán, cho biết lần gia hạn lâu nhất của mỗi lần ñăng kí là bao nhiêu ngày
Lưu ý: Không xét trường hợp người bán không ñăng kí hoặc không gia hạn.
Kết quả gồm: Họ tên người bán, ngày ñăng kí, số ngày của lần gia hạn lâu nhất. Các dòng dữ liệu kết quả không
ñược trùng nhau.
6. Với mỗi ñịa ốc có ñăng kí quảng cáo trong năm 2005, cho biết lần ñăng kí quảng cáo lâu nhất trong năm 2005 của
ñịa ốc ñó kéo dài bao nhiêu ngày.
Lưu ý: Chỉ xét lần ñăng kí ñầu tiên, không xét các lần gia hạn
Kết quả xuất ra gồm: Mã ñịa ốc, số, ñường, phường, quận, số ngày của lần ñăng kí lâu nhất, các dòng dữ liệu kết

quả không ñược trùng nhau
7. Cho biết danh sách những người bán ñăng kí quảng cáo nhiều ñịa ốc khác nhau nhất trong năm 2005 (ngày ñăng ký).
Thông tin mỗi người bán cần xuất ra gồm mã người bán, họ tên, ñịa chỉ theo ñúng thứ tự này, các dòng dữ liệu kết
quả không ñược trùng nhau.
8. Với mỗi người bán cho biết họ ñã thực hiện ñăng ký quảng cáo bao nhiêu lần. Lưu ý: Số lần ñăng ký quảng cáo tính
luôn cả những lần gia hạn.
Thông tin mỗi người bán cần xuất ra gồm mã người bán, họ tên, ñịa chỉ và số lần ñăng ký quảng cáo theo ñúng thứ
tự này, các dòng dữ liệu kết quả không ñược trùng nhau.
9. Với mỗi ñịa ốc cho biết nó ñã ñược ñăng ký quảng cáo bao nhiêu lần. Lưu ý: Số lần ñăng ký quảng cáo tính luôn cả
những lần gia hạn.
Thông tin mỗi người bán cần xuất ra gồm mã ñịa ốc, số, ñường, phường, quận và số lần ñăng ký quảng cáo theo
ñúng thứ tự này, các dòng dữ liệu kết quả không ñược trùng nhau.
10. Cho biết danh sách những người bán ñăng kí quảng cáo nhiều ñịa ốc ở quận 5 nhất.
Thông tin mỗi người bán gồm họ tên người bán và số lượng ñịa ốc mà người ñó ñăng kí.. Lưu ý là một ñịa ốc ñược
ñăng ký (hay gia hạn) nhiều lần thì vẫn ñược tính là 1.
Phép chia:
11. Cho biết những người bán có ít nhất 1 lần ñăng kí trong ñó ñăng kí tất cả các dịch vụ hiện có.
Thông tin người bán cần xuất gồm mã người bán, họ tên, ñịa chỉ theo ñúng thứ tự này, các dòng dữ liệu kết quả
không ñược trùng nhau.

Bộ Môn HTTT – Khoa CNTT – ĐH KHTN TPHCM

-6-


Bài tập thực hành

CSDL – TH107

12. Cho biết những người bán nào ñã ñăng kí qua tất cả các dịch vụ có giá tiền từ 200.000 trở lên. Tất cả dịch vụ không

nhất thiết phải nằm trong 1 lần ñăng ký, miễn là qua tất cả các lần ñăng ký, người bán ñó sử dụng qua tất cả các dịch
vụ thỏa ñiều kiện giá tiền nêu trên.
Thông tin người bán cần xuất gồm mã người bán, họ tên, ñịa chỉ theo ñúng thứ tự này, các dòng dữ liệu kết quả
không ñược trùng nhau.
13. Cho biết danh sách các ñịa ốc ñã ñược ñăng ký quảng cáo qua với tất cả các dịch vụ có tiền dịch vụ trong khoảng từ
100.000 ñến 300.000. Tất cả dịch vụ không nhất thiết phải nằm trong 1 lần ñăng ký, miễn là qua tất cả các lần ñăng
ký, ñịa ốc ñó sử dụng qua tất cả các dịch vụ thỏa ñiều kiện giá tiền nêu trên.
Thông tin ñịa ốc cần xuất gồm mã ñịa ốc, số, ñường, phường, quận theo ñúng thứ tự này, các dòng dữ liệu kết quả
không ñược trùng nhau.
14. Cho biết danh sách những ñịa ốc có ít nhất một lần ñăng ký sử dụng tất cả các dịch vụ hiện có.
Thông tin ñịa ốc cần xuất gồm mã ñịa ốc, số, ñường, phường, quận theo ñúng thứ tự này, các dòng dữ liệu kết quả
không ñược trùng nhau.
15. Cho biết danh sách những người bán có ít nhất một lần ñăng ký vào tháng 10 (ngày ñăng ký) trong ñó sử dụng tất cả
các dịch vụ có giá tiền trong khoảng từ 100.000 ñến 200.000.
Thông tin ñịa ốc cần xuất gồm mã người bán, họ tên, ñịa chỉ, số ñiện thoại theo ñúng thứ tự này, các dòng dữ liệu
kết quả không ñược trùng nhau.

Bộ Môn HTTT – Khoa CNTT – ĐH KHTN TPHCM

-7-