Bài tập cơ sở dữ liệu | Học viện Báo chí và Tuyên truyền

Lược đồ CSDL sau được sử dụng cho Câu 1, 2 và 3.
Hair Beauty Salon ABC cần xây dựng hệ thống quản lý việc yêu cầu làm đẹp từ các khách
hàng như cắt tóc, chăm sóc da, …). Dưới đây là một phần CSDL quản lý các yêu cầu sửa
chữa, trong đó mỗi lược đồ quan hệ cho trước các khoá chính (là tập thuộc tính được gạch
dưới) và các tập phụ thuộc hàm định nghĩa tương ứng cho từng lược đồ quan hệ.
PHIEU_YEU_CAU (MaPhieu, MãKH, HọTên, ĐiệnThoại, ĐịaChỉ, TGLập, TổngTiền) Mô tả:
Mỗi phiếu yêu cầu có một mã phiếu (MaPhieu) duy nhất, thời gian lập phiếu (TGLập), thực
hiện cho một khách hàng nào đó. Mỗi khách hàng cần lưu một mã bao gồm mã khách hàng
(MãKH), họ tên (HọTên), địa chỉ (ĐịaChỉ), điện thoại (ĐiệnThoại).Mỗi phiếu yêu cầu có tổng tiền (TổngTi )
ền thực hiện và mua sản phẩm của phiếu yêu cầu đó. Tập phụ thuộc hàm được định nghĩa
cho lược đồ quan hệ PHIEU_YEU_CAU như sau:
F1 = { f11: MaPhieu → MãKH, HọTên, ĐiệnThoại, ĐịaChỉ, TGLập, TổngTiền;
f12: MãKH → HọTên, ĐiệnThoại, ĐịaChỉ }
PHỤ_TRÁCH_DV (MaPhieu, MaDV, TênDV, MaNV, TênNV, TGBắtĐầu, TGHoànTất, GiaTienDV)
Mô tả: Mỗi phiếu yêu cầu sẽ có nhiều dịch vụ, với mỗi mã dịch vụ có một tên dịch vụ tương
ứng (TenDV), sẽ có một nhân viên phụ trách (mã nhân viên, tên nhân viên), có thời gian bắt
đầu (TGBắtĐầu) và thời gian hoàn tất (TGHoànTất). Một số tên dịch vụ phổ biến như: “Cắt
tóc”, “Gội Đầu” ,… Tập phụ thuộc hàm được định nghĩa cho lược đồ quan
hệ PHỤ_TRÁCH_DV như sau:
F2 = { f21: MaPhieu, MaDV → TênDV, MaNV, TênNV, TGBắtĐầu, TGHoànTất, GiaTienDV; f22: MaDV → TenDV; f23: MaNV → TênNV }
SAN_PHAM (MaSP, TênSP, GiáTiền)
Mô tả: Mỗi sản phẩm sẽ có một mã sản phẩm (MaSP) duy nhất, tên và giá tiền mặc định
tương ứng trên một đơn vị sản phẩm. Tập phụ thuộc hàm được định nghĩa cho lược đồ quan hệ SAN_PHAM như sau:
F3 = {f31: MaSP → TênSP, GiáTiền}
CT_SANPHAM (MaPhieu, MaSP, SốLượng, GiáTiền, ThànhTiền) Mô tả:
Danh sách sản phẩm được sử dụng mỗi phiếu dịch vụ của khách hàng. Mỗi phiếu yêu cầu có
thể sử dụng nhiều sản phẩm với số lượng (SốLượng), giá bán (GiáTiền) và thành tiền
(ThànhTiền) tương ứng. Tập phụ thuộc hàm được định nghĩa cho lược đồ quan hệ CT_SANPHAM như sau:
F4 = { f41: MaPhieu, MaSP→ SốLượng, ĐơnGiá, ThànhTiền} CÂU HỎI:
Câu 1. Hãy biểu diễn các yêu cầu truy vấn sau bằng cả 2 ngôn ngữ đại số quan hệ và SQL (3.0 điểm).
Cho biết danh sách những phiếu yêu cầu có sử dụng dịch vụ tên “Cắt tóc” và có mua sản
phẩm có tên “Dầu gội đầu Dove”. Xuất ra mã phiếu, ngày lập phiếu, họ tên và điện thoại của khách hàng.
Cho biết số tổng thành tiền sử dụng sản phẩm của những phiếu yêu cầu sử dụng từ 2 sản phẩm
trở lên, không sử dụng dịch vụ tên “Cắt tóc”. Xuất ra mã phiếu, thời gian lập phiếu, họ tên,
tổng tiền sử dụng sản phẩm.
Câu 2. Hãy xác định bối cảnh, nội dung bằng ngôn ngữ hình thức và bảng tầm ảnh hưởng của RBTV sau (1.5 điểm):
“Thời gian bắt đầu thực hiện của các dịch vụ trong phiếu yêu cầu phải sau thời gian lập
phiếu và trước thời gian hoàn tất”
Câu 3. Đánh giá chất lượng lược đồ CSDL (1.5 điểm):
a) Hãy chỉ ra những điểm trùng lặp dữ liệu trên lược đồ CSDL trê n.
b) Hãy xác định dạng chuẩn của từng lược đồ quan hệ? Giải thích và kết luận dạng chuẩn
của lược đồ CSDL. Hãy chuẩn hóa các lược đồ quan hệ để lược đồ CSDL đạt được dạng chuẩn BCNF. BÀI GIẢI Lược đồ CSDL PHIEU_YEU_CAU MaPhieu (
, MãKH, HọTên, ĐiệnThoại, ĐịaChỉ, TGLập, TổngTiền)
PHỤ_TRÁCH_DV (MaPhieu, MaDV, TênDV, MaNV, TênNV, TGBắtĐầu, TGHoànTất, GiaTienDV)
SAN_PHAM (MaSP, TênSP, GiáTiền)
CT_SANPHAM (MaPhieu, MaSP, SốLượng, GiáTiền, ThànhTiền)
Câu 1. Hãy biểu diễn các yêu cầu truy vấn sau bằng cả 2 ngôn ngữ đại số quan hệ và SQL (3.0 điểm).
a. Cho biết danh sách những phiếu yêu cầu có sử dụng dịch vụ tên “Cắt tóc” và có mua sản phẩm
có tên “Dầu gội đầu Dove”. Xuất ra mã phiếu, ngày lập phiếu, họ tên và điện thoại của khách hàng. (1.5 đ) ĐSQH (0.75 đ)
DV ← σTENDV=’Cắt tóc’ (PHỤ_TRÁCH_DV) 0.25
SP ← σTênSP=’Dầu gội đầu Dove’ (CT_SANPHAM SAN_PH * AM) 0.25 PH ← PHIEU_YEU_CAU
KQ ← Π MaPhieu, TGLap, HọTên, ĐIệnThoại ((PH ⋈PH.MaPhieu=DV.MaPhieu DV) ⋈PH.MaSP=SP.MaSP SP) 0.25 SQL (0.75 đ) SELECT PH.* FROM PHỤ_TRÁCH_DV DV, C
T_SANPHAM C, SAN_PHAM S, PHIEU_YEU_CAU PH
WHERE C.MASP=S.MASP AND PH.MAPHIEU=DV.MAPHIEU AND
PH.MAPHIEU=C.MAPHIEU AND DV.TênDV=’Cắt tóc' AND S.TENSP=’Dầu gội đầu Dove' b. Cho b ết số tổng t i
hành tiền sử dụng sản phẩm của những phiếu yêu cầu sử dụng từ 2 sản
phẩm trở lên, không sử dụng dịch vụ tên “Cắt tóc”. Xuất ra mã phiếu, thời gian lập phiếu, họ tên,
tổng tiền sử dụng sản phẩm. ĐSQH (0.75 đ)
R1 ← Π MaPhieu PHIEU_YEU_CAU - Π MaPhieu (σ TENDV=’Cắt tóc’PHỤ_TRÁCH_DV)
R2 ← (PHIEU_YEU_CAU * R1) * CT_SANPHAM
R3 (MaPhieu, TGLap, HoTen, TongTienSP, SoLuongSP) ← MaPhieu, TGLap, HoTen ℑ SUM(ThànhTiền), COUNT(MaSP) R2 KQ Π
← MaPhieu, TGLap, HoTen, TongTienSP (σ SoLuongSP>=2 (R3)) SQL (0.75đ)
SELECT P.MaPhieu, P.TGLập, P.HọTên, SUM(C.ThanhTien) as TongTienSP FROM PHIEU_YEU_CAU P, CT_SANPHAM C
WHERE P.MaPhieu = C.MaPhieu AND
P.MaPhieu NOT IN (SELECT MaPhieu FROM PHỤ_TRÁCH_DV WHERE TenDV = ‘Cắt tóc’)
GROUP BY P.MaPhieu, P.TGLập, P.HọTên HAVING COUNT(C.MaSP) >= 2
Câu 2. Hãy xác định bối cảnh, nội dung bằng ngôn ngữ hình thức và bảng tầm ảnh hưởng của RBTV sau (1.5 điểm):
“Thời gian bắt đầu thực hiện của các dịch vụ trong phiếu yêu cầu phải sau thời gian lập phiếu và
trước thời gian hoàn tất”
Bối cảnh: PHỤ_TRÁCH_DV , PHIEU_YEU_CAU (0.25 đ) Nội dung (0.75):
∀ p∈ PHIEU_YEU_CAU, ∀ q∈ PHỤ_TRÁCH_DV (q.TGBatDau <= q.TGKThuc ∧ q.MaPhieu
= p.MaPhieu → q.TGBatDau <= p.TGLap)
Bảng tầm ảnh hưởng: (0.5 đ) T X S PHIEU_YEU_CAU - - + (TGLap) PHỤ_TRÁCH_DV + - + (TGBatDau, TGKThuc)
Câu 4. Chuẩn hoá lược đồ trên (1.5 điểm) a)
Hãy chỉ ra những điểm trùng lắp dữ liệu trên lược đồ CSDL trên. (0.5 đ)
(0.25) Quan hệ PHIEU_YEU_CAU và PHỤ_TRÁCH_DV có trùng lặp dữ liệu
PHIEU_YEU_CAU trùng lắp trên tập thuộc tính {MãKH,HọTên, ĐiệnThoại, ĐịaChỉ} do tồn tại
phụ thuộc hàm MãKH → HọTên, ĐiệnThoại, ĐịaChỉ
(0.25) Quan hệ PHỤ_TRÁCH_DV trùng lặp trên tập thuộc tính { MaDV,TenDV} và
{ MaNV,TênNV} do tồn tại phụ thuộc hàm MaDV → TenDV, và MaNV → TênNV b)
Hãy xác định dạng chuẩn của từng lược đồ quan hệ ? Giải thích và kết luận dạng chuẩn
của lược đồ CSDL? (0.5 đ)
1/ PHIEU_YEU_CAU: đạt dạng chuẩn 2, do có phụ thuộc hàm bắc cầu vào khoá (f12: MãKH
→ HọTên, ĐiệnThoại, ĐịaChỉ) nên vi phạm dạng chuẩn 3, ngoài ra khoá chỉ là có 1 thuộc tính
nên tất cả các thuộc tính còn lại đều phụ thuộc đầy đủ vào khoá. (0.1đ)
2/ PHỤ_TRÁCH_DV: đạt dạng chuẩn 1, do vi phạm dạng chuẩn 2 vì tồn tại PTH không phụ
thuộc đầy đủ vào khoá (MaDV → TenDV). (0.1đ)
3/ SAN_PHAM (MaSP, TênSP, GiáTiền): Dạng chuẩn BCNF, giải thích: Theo định nghĩa, lược
đồ c ỉ có 1 phụ thuộc hàm và vế trái là khoá/siêu khoá (0.1đ) h 4/ CT_SANPHAM MaPhieu, MaSP ( , SốL ợng, G ư
iáTiền, ThànhTiền): Dạng chuẩn BCNF, giải
thích: Theo định nghĩa, lược đồ c ỉ có 1 phụ thuộ h
c hàm và vế trái là khoá/siêu khoá (0.1đ)
Dạng chuẩn của lược đồ CSDL: Dạng chuẩn 1. Giải thích: Do dạng chuẩn thấp nhất là dạng chuẩn1 (0.1đ) c)
Hãy chuẩn hoá các lược đồ quan hệ để l ợc đồ CSD ư L đạt dạng chuẩn BCNF.
Chuẩn hoá các lược đồ có dạng chuẩn thấp: (0.5 đ)
(0.2 đ) Chuẩn hoá PHIEU_YEU_CAU: tách bảng dựa trên PTH gây ra dạng chuẩn thấp (f12:
MãKH → HọTên, ĐiệnThoại, ĐịaChỉ) ta có các bảng sau khi tách: PHIEU_YEU_CAU MaPhieu (
, MãKH, TGLập, TổngTiền)
KHÁCH_HANG (MãKH, HọTên, ĐiệnThoại, ĐịaChỉ)
(0.2 đ) Chuẩn hoá PHỤ_TRÁCH_DV: tách bảng dựa trên PTH gây ra vi phạm dạng chuẩn 2 (f22:
MaDV → TenDV) ta có các bảng sau khi tách. Sau khi tách ta thấy vẫn còn PTH bắc cầu gây ra
vi phạm dạng chuẩn 3 (f23: MaNV → TênNV )
nên thực hiện tách tiếp. Kết quả ta có các bảng sau:
SUDUNG_DV (MaPhieu, MaDV, MaNV, TGBắt ầ
Đ u, TGHoànTất, GiaTienDV) DICH_VU(MaDV, TênDV) NHAN_VIEN (MaNV, TênNV)
Các quan hệ còn lại SAN_PHAM, CT_SAN_PHAM đã đạt DC BCNF
(0.1 đ) Sau khi tách, mỗi lược đồ quan hệ còn 1 PTH và thoả điều kiện của DC BCNF.
Lược đồ CSDL đạt BCNF vì tất cả l ợc đồ quan ư hệ đạt DC BCNF.