Bài tập hệ quản trị cơ sở dữ liệu số 2 | Sinh viên: Chu Trọng Tấn - MSSV: K235480106063
Phần 1: Thiết kế và Khởi tạo Cấu trúc Dữ liệu
Phần 2: Xây dựng Function
Phần 3: Xây dựng Store Procedure
Phần 4: Trigger và Xử lý logic nghiệp vụ
Phần 5: Cursor và Duyệt dữ liệu
Database: Quanlyphonggame_K235480106063
Database và code của 3 bảng
[MaThanhVien] trong bảng [ThanhVien]: định danh duy nhất cho mỗi thành viên
Bảng thành viên
[MaMayTinh] trong bảng [MayTinh]: Định danh duy nhất cho mỗi máy tính
Bảng máy tính
[MaHoaDon] trong bảng [HoaDon]: Định danh duy nhất cho mỗi hoá đơn
Bảng hoá đơn
[MaThanhVien] trong bảng [HoaDon] → Tham chiếu đến [ThanhVien]
[MaMayTinh] trong bảng [HoaDon] → Tham chiếu đến [MayTinh]
*ON DELETE CASCADE: Nếu xóa thành viên, xóa luôn các hoá đơn của thành viên đó
[TrangThaiTaiKhoan]: Chỉ chấp nhận: 'Hoat_Dong', 'Binh_Ngo', hoặc 'Khoai_Hoan'
[GiaThueGioTinh]: Phải > 0 (không thể có giá âm)
[NamPhatHanhMay]: Phải nằm trong khoảng 2010-2030
[TrangThaiMay]: Chỉ chấp nhận: 'San_Sang', 'Dang_Su_Dung', 'Bao_Tri', 'Hoi_Hang'
[TrangThaiThanhToan]: Chỉ chấp nhận: 'Chua_Thanh_Toan', 'Da_Thanh_Toan', 'Hoan_Tien'
SQL Server có các nhóm hàm chính:
a) String Functions - Xử lý chuỗi
LEN: Đếm độ dài chuỗi
SELECT LEN(N'Xin chào') AS [ĐộDài];
UPPER/LOWER: Chuyển hoa/thường
SELECT UPPER(N'Remu') AS [InHoa], LOWER(N'REMU') AS [InThường];
SUBSTRING: Cắt chuỗi
SELECT SUBSTRING(N'SQL Server', 1, 3) AS [KếtQuả];
CONCAT: Nối chuỗi
SELECT CONCAT(N'Họ: ', N'Nguyễn', N' Tên: ', N'Văn A') AS [HọTên];
b) Date Functions - Xử lý ngày tháng
GETDATE: Lấy ngày giờ hiện tại
SELECT GETDATE() AS [NgàyGiờHiệnTại];
DATEADD: Cộng thêm ngày/tháng/năm
SELECT DATEADD(DAY, 7, GETDATE()) AS [Sau7Ngày];
DATEDIFF: Tính khoảng cách giữa 2 ngày
SELECT DATEDIFF(YEAR, '2000-01-01', GETDATE()) AS [SốTuổi];
FORMAT: Định dạng ngày
SELECT FORMAT(GETDATE(), 'dd/MM/yyyy') AS [NgàyViệtNam];
c) Math Functions - Toán học
ROUND: Làm tròn
SELECT ROUND(8.567, 2) AS [LàmTròn];
CEILING/FLOOR: Làm tròn lên/xuống
SELECT CEILING(8.1) AS [LàmTrònLên], FLOOR(8.9) AS [LàmTrònXuống];
ABS: Giá trị tuyệt đối
SELECT ABS(-100) AS [GiáTrịTuyệtĐối];
d) Aggregate Functions - Tổng hợp
COUNT, SUM, AVG, MIN, MAX
SELECT
COUNT(*) AS [TổngSốBảnGhi],
AVG([Điểm]) AS [ĐiểmTrungBình],
MAX([Điểm]) AS [ĐiểmCaoNhất]
FROM [BảngĐiểm];
Mục đích:
- Tái sử dụng logic phức tạp
- Đóng gói business rules
- Làm code dễ đọc, dễ bảo trì
3 loại chính:
a) Scalar Function - Trả về 1 giá trị đơn
- Dùng khi: Tính toán 1 giá trị từ nhiều tham số (tính tuổi, tính điểm trung bình có trọng số, format dữ liệu đặc biệt)
b) Inline Table-Valued Function - Trả về bảng (1 câu SELECT)
- Dùng khi: Lọc dữ liệu theo điều kiện phức tạp, có thể JOIN với bảng khác
- Performance tốt nhất trong 3 loại
c) Multi-statement Table-Valued Function - Trả về bảng (có logic phức tạp)
- Dùng khi: Cần xử lý nhiều bước, dùng biến, vòng lặp trước khi trả kết quả
Tại sao cần tự viết?
- System functions chỉ xử lý logic chung chung
- Business logic của mỗi dự án khác nhau (VD: Cách tính lương, cách tính điểm, quy tắc giảm giá riêng)
Tính tổng tiền đã chi của một thành viên
Code Scalar Function
Lấy danh sách máy tính theo vị trí phòng
Code Inline Table-Value Function
Báo cáo tình trạng máy tính theo phòng
Code Multi-Statement Table-Valued Function
Dữ liệu của 3 bảng
Scalar Function
Tổng chi của từng thành viên
Inline Table-Valued Function
Danh sách máy Phòng VIP
Multi-Statement Table-Valued Function
Phòng cần xử lý gấp (tỷ lệ sẵn sàng < 50%)
Stored Procedure (SP) là một khối lệnh SQL được lưu sẵn trong database, đặt tên và có thể gọi lại nhiều lần. Giống như viết sẵn một "công thức nấu ăn" — mỗi lần cần chỉ việc gọi tên, không phải viết lại từ đầu.
┌──────────────────────────────────────┐
│ STORED PROCEDURE │
│ │
│ Input (@tham_số) ──► Xử lý logic │
│ │ │
│ ┌─────┴──────┐ │
│ ▼ ▼ │
│ OUTPUT trả Result Set │
│ về giá trị (bảng KQ) │
└──────────────────────────────────────┘
┌──────────────────┬─────────────────────────┬─────────────────────────────────────┐
│ SP hệ thống │ Chức năng │ Ví dụ │
├──────────────────┼─────────────────────────┼─────────────────────────────────────┤
│ sp_help │ Xem thông tin đối tượng │ EXEC sp_help 'ThanhVien' │
├──────────────────┼─────────────────────────┼─────────────────────────────────────┤
│ sp_helptext │ Xem mã nguồn │ EXEC sp_helptext 'fn_TongTienDaChi' │
├──────────────────┼─────────────────────────┼─────────────────────────────────────┤
│ sp_columns │ Xem cột của bảng │ EXEC sp_columns 'HoaDon' │
├──────────────────┼─────────────────────────┼─────────────────────────────────────┤
│ sp_tables │ Liệt kê bảng │ EXEC sp_tables │
├──────────────────┼─────────────────────────┼─────────────────────────────────────┤
│ sp_databases │ Liệt kê database │ EXEC sp_databases │
├──────────────────┼─────────────────────────┼─────────────────────────────────────┤
│ sp_who / sp_who2 │ Xem phiên kết nối │ EXEC sp_who2 │
├──────────────────┼─────────────────────────┼─────────────────────────────────────┤
│ sp_rename │ Đổi tên đối tượng │ EXEC sp_rename 'A', 'B' │
├──────────────────┼─────────────────────────┼─────────────────────────────────────┤
│ sp_helpindex │ Xem index │ EXEC sp_helpindex 'HoaDon' │
└──────────────────┴─────────────────────────┴─────────────────────────────────────┘
Bài toán
Nạp tiền vào tài khoản thành viên. Cần kiểm tra:
- Thành viên có tồn tại không?
- Tài khoản có đang hoạt động không?
- Số tiền nạp có hợp lệ không (> 0)?
Code Insert/Update
Khai thác:
Trường hợp thành công
Trường hợp lỗi 1: Số tiền âm
Trường hợp lỗi 2: Thành viên không tồn tại
Trường hợp lỗi 3: TK bị khóa (TV mã 4 — Binh_Ngo)
Kiểm tra lại số dư sau khi nạp
Bài toán Tính tiền cho một phiên sử dụng máy khi khách rời đi. SP cần: - Cập nhật thời gian kết thúc, số giờ, thành tiền vào bảng HoaDon - Trả ra ngoài (OUTPUT) tổng tiền phải thanh toán để hiển thị cho nhân viên
Code có tham số output
Khai thác:
Trường hợp thành công
Trường hợp lỗi: hóa đơn không tồn tại
Bài toán
Xem lịch sử sử dụng chi tiết: thông tin thành viên + máy đã dùng + hóa đơn. Lọc theo khoảng thời gian và tùy chọn theo phòng.
Code Result set
Khai thác:
Xem tất cả phiên trong tháng 1/2026
Ngày không hợp lệ
Bài toán nghiệp vụ:
Thực tế phòng game: Khi nhân viên tạo hóa đơn mới (INSERT vào HoaDon) nghĩa là khách bắt đầu ngồi máy → máy đó phải tự động chuyển sang trạng thái Dang_Su_Dung trong bảng MayTinh.
Ngược lại, khi hóa đơn được cập nhật ThoiGianKetThuc (UPDATE HoaDon) nghĩa là khách rời máy → máy đó phải tự động chuyển về San_Sang.
Nếu không có trigger, nhân viên phải nhớ cập nhật tay → dễ quên → dữ liệu sai.
Sơ đồ nghiệp vụ:
Khách vào ngồi máy Khách rời máy
│ │
▼ ▼
INSERT [HoaDon] UPDATE [HoaDon]
(tạo hóa đơn mới) (ghi ThoiGianKetThuc)
│ │
▼ ▼
┌─ TRIGGER tự động ──┐ ┌─ TRIGGER tự động ──┐
│ UPDATE [MayTinh] │ │ UPDATE [MayTinh] │
│ SET Dang_Su_Dung │ │ SET San_Sang │
└────────────────────┘ └────────────────────┘
Code Trigger 1
Code Trigger 2
Xem trạng thái máy trước khi test
INSERT hóa đơn → Trigger tự chuyển máy
UPDATE hóa đơn (khách rời) → Trigger tự trả máy
Trường hợp bị từ chối
Bài toán
Thử viết:
- Trigger trên HoaDon (Bảng A): Khi INSERT → UPDATE MayTinh (Bảng B)
- Trigger trên MayTinh (Bảng B): Khi UPDATE → UPDATE ngược HoaDon (Bảng A)
Mục đích: Quan sát chuyện gì xảy ra khi 2 trigger "gọi nhau".
Trigger trên Bảng A (HoaDon) — đã có ở trên
Trigger trên Bảng B (MayTinh) — trigger ngược
Code vòng lặp
Thực hiện thử nghiệm
Đảm bảo máy 103 (VIP-03) đang San_Sang
Tắt tạm trigger MayTinh để UPDATE trên không kích hoạt vòng lặp
Bật lại trigger
INSERT hóa đơn
Code trigger không kiểm tra điều kiện (chỉ cho vào file sql chứ không cho chạy)
Giả sử nếu chạy thì thông báo lỗi của hệ thống như sau:
Msg 217, Level 16, State 1, Procedure trg_VONG_LAP_A, Line X
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).
Giải thích thông báo:
┌────────────────────────┬──────────────────────────────────────────────────────────┐
│ Thành phần │ Ý nghĩa │
├────────────────────────┼──────────────────────────────────────────────────────────┤
│ Msg 217 │ Mã lỗi: vượt quá giới hạn lồng nhau (nesting) │
├────────────────────────┼──────────────────────────────────────────────────────────┤
│ Level 16 │ Mức nghiêm trọng: lỗi do người dùng gây ra │
├────────────────────────┼──────────────────────────────────────────────────────────┤
│ State 1 │ Trạng thái lỗi cụ thể │
├────────────────────────┼──────────────────────────────────────────────────────────┤
│ Nesting level exceeded │ Trigger gọi nhau vượt quá 32 tầng │
├────────────────────────┼──────────────────────────────────────────────────────────┤
│ (limit 32) │ SQL Server cho phép tối đa 32 lần lồng trigger/procedure │
└────────────────────────┴──────────────────────────────────────────────────────────┘
| Tình huống | Kết quả | Nguyên nhân |
|---|---|---|
| Trigger A → B (1 chiều) | ✅ An toàn | Không có vòng lặp |
| Trigger A → B → A (có điều kiện dừng) | ✅ An toàn | Chuỗi tự dừng khi điều kiện không khớp |
| Trigger A ↔ B (không điều kiện dừng) | ❌ Lỗi Msg 217 | Vòng lặp vượt 32 tầng → ROLLBACK |
| Trigger A → A (recursive, OFF) | ✅ Không chạy lại | RECURSIVE_TRIGGERS mặc định OFF |
| Trigger A → A (recursive, ON) | Tự gọi chính mình → có thể vượt 32 tầng |
Cursor là cơ chế cho phép duyệt từng dòng một trong tập kết quả SELECT, thay vì xử lý cả bảng cùng lúc.
SQL thông thường (Set-based): CURSOR (Row-by-row):
┌──────────────────────┐ ┌──────────────────────┐
│ Dòng 1 │ │► Dòng 1 ← xử lý │
│ Dòng 2 │ Xử lý │ Dòng 2 │
│ Dòng 3 │ CÙNG LÚC │ Dòng 3 │
│ Dòng 4 │ ══════► │ Dòng 4 │
│ Dòng 5 │ Kết quả │ Dòng 5 │
└──────────────────────┘ └──────────────────────┘
│
▼
┌──────────────────────┐
│ Dòng 1 │
│► Dòng 2 ← xử lý │
│ Dòng 3 │
│ ...lặp lại... │
└──────────────────────┘
Vòng đời của CURSOR
DECLARE → OPEN → FETCH NEXT → WHILE → CLOSE → DEALLOCATE
(khai (mở (lấy dòng (lặp (đóng (giải phóng
báo) cursor) đầu tiên) xử lý) cursor) bộ nhớ)
Bài toán nghiệp vụ:
Duyệt qua từng thành viên đang hoạt động, tính tổng tiền đã chi, tổng giờ sử dụng, số lần đến → phân hạng → cập nhật cột GhiChu với thông tin chi tiết riêng cho từng người.
Code sử dụng Cursor
Tổng hợp kết quả của code Cursor
| ID | Họ và Tên | Hạng | Chi phí | Tổng giờ | Số lần | Thâm niên |
|---|---|---|---|---|---|---|
| TV 1 | Nguyễn Văn An | [Bạc] | 440,000đ | 15.00h | 3 | 830 ngày |
| TV 2 | Trần Thị Bích | [Bạc] | 120,000đ | 6.00h | 2 | 804 ngày |
| TV 3 | Lê Hoàng Cường | [VIP] | 1,405,000đ | 36.00h | 5 | 780 ngày |
| TV 5 | Hoàng Thị Lan | [Bạc] | 192,000đ | 7.00h | 2 | 712 ngày |
| TV 6 | Võ Đức Mạnh | [Vàng] | 590,000đ | 19.00h | 4 | 692 ngày |
| TV 7 | Đặng Thùy Ngọc | [Thường] | 30,000đ | 2.00h | 1 | 645 ngày |
| TV 8 | Bùi Quốc Phong | [Vàng] | 795,000đ | 20.00h | 3 | 469 ngày |
| TV 10 | Ngô Thanh Sơn | [Thường] | 0đ | 3.00h | 1 | 323 ngày |
Code không dùng Cursor
Thông báo hệ thống khi chạy
Kiểm tra kết quả hoàn toàn giống nhau
Bài toán: Tính số dư lũy kế theo thời gian cho từng thành viên
Với mỗi thành viên, duyệt qua từng hóa đơn theo thứ tự thời gian, tính số dư còn lại sau mỗi lần thanh toán.
Nếu số dư âm tại bất kỳ thời điểm nào → ghi cảnh báo NỢ kèm thời điểm bắt đầu nợ.
In ra lịch sử biến động chi tiết.
Code cho bài toán 4
Kết quả