44 trang 17 lượt tải

Báo cáo cuối kỳ Cơ Sở Dữ Liệu Nâng Cao - Thực hành với SQL Server | Môn Cơ sở dữ liệu - Trường Đại học Bách khoa Hà Nội

34

Báo cáo cuối kỳ Cơ Sở Dữ Liệu Nâng Cao - Thực hành với SQL Server . Tài liệu được sưu tầm, biên soạn dưới dạng file PDF gồm 44 trang, hi vọng tài liệu này sẽ giúp các bạn học tốt, ôn tập hiệu quả, đạt kết quả cao trong các bài thi, bài kiểm tra sắp tới. Mời các bạn cùng tham khảo chi tiết bài viết dưới đây nhé.

Môn: Cơ sở dữ liệu ( BKA) 35 tài liệu

Trường: Đại học Bách Khoa Hà Nội 2.8 K tài liệu

Tác giả:

Profile

Lê Phương

2 tuần trước
Danh sách Quiz
/44
M
lOMoARcPSD| 45315597
lOMoARcPSD|45315597
MỤC LỤC
Mục lục
1 Mô tả về bộ dữ liệu .............................................................................................................................................. 5
2 Bài tập .................................................................................................................................................................... 8
2.1 Bài tập 1 .............................................................................................................................................................. 8
2.2 Bài tập 2 ............................................................................................................................................................ 11
2.3 Bài tập 3 ............................................................................................................................................................ 15
2.4 Bài tập 4 ............................................................................................................................................................ 18
2.5 Bài tập 5 ............................................................................................................................................................ 21
2.6 Bài tập 6 ............................................................................................................................................................ 23
2.7 Bài tập 7 ............................................................................................................................................................ 26
2.8 Bài tập 8 ............................................................................................................................................................ 30
2.9 Bài tập 9 ............................................................................................................................................................ 33
2.10 Bài tập 10........................................................................................................................................................ 36
3 Kết luận và các hướng phát triển ..................................................................................................................... 41
lOMoARcPSD| 45315597
MỤC LỤC
Lời cảm ơn
Trong học kỳ này, tác giả đã có cơ hội học tập môn Cơ sở dữ liệu nâng cao, một học phần có tính
ứng dụng cao và mang lại nhiều giá trị thực tiễn trong lĩnh vực nghiên cứu và kỹ thuật. Xin trân trọng
cảm ơn TS. Trần Ngọc Thăng, giảng viên phụ trách môn học, vì sự hướng dẫn tận tình và định hướng
chuyên môn rõ ràng trong suốt quá trình học cũng như khi thực hiện đề tài báo cáo.
Những nội dung được truyền đạt trong môn học, cùng với phương pháp giảng dạy rõ ràng, logic và
có hệ thống của thầy, đã giúp chúng em tiếp cận hiệu quả với các khái niệm và kỹ thuật lập trình song
song vốn có tính trừu tượng cao. Các gợi ý chuyên môn từ thầy đã hỗ trợ đáng kể trong việc hoàn thiện
báo cáo này.
Dù đã nỗ lực thực hiện một cách nghiêm túc, song bài báo cáo vẫn có thể tồn tại một số hạn chế
và lỗi sai sót. Rất mong nhận được ý kiến đóng góp từ thầy để có thể cải thiện và nâng cao chất lượng
báo cáo trong tương lai.
Xin trân trọng cảm ơn.
lOMoARcPSD| 45315597
MỤC LỤC
Lời mở đầu
Trong bối cảnh thế giới ngày nay, sự phát triển bùng nổ của ngành công nghệ thông tin cùng với
nhu cầu khoa học kỹ thuật áp dụng cho đời sống đã khiến lượng dữ liệu sinh ra và cần xử lý tăng lên
theo cấp số nhân từng giờ từng ngày. Đi kèm với sự bùng nổ về mặt dữ liệu, hiệu suất truy vấn và xử lý
dữ liệu ngày càng trở nên quan trọng. Đặc biệt, trong các hệ thống cơ sở dữ liệu lớn hoặc yêu cầu thời
gian thực, việc áp dụng các kỹ thuật tối ưu truy vấn SQL nâng cao là điều tất yếu.
Việc tối ưu câu lệnh SQL không chỉ giúp tăng tốc độ truy vấn dữ liệu mà còn mở rộng khả năng xử
lý các bài toán phức tạp, tận dụng được sức mạnh của các hệ quản trị cơ sở dữ liệu hiện đại. Trong đồ
án này, tác giả xin trình bày cách áp dụng các kỹ thuật SQL nâng cao để giải quyết các bài toán cụ thể ,
qua đó thể hiện sức mạnh của tối ưu truy vấn và nâng cao hiệu suất xử lý dữ liệu. Việc áp dụng các kỹ
thuật SQL nâng cao như subquery, window functions, indexing, optimization hints sẽ mang lại hiệu quả
đáng kể khi dữ liệu mở rộng (bảng dữ liệu lớn).
Thông qua đồ án này, tác giả mong muốn thể hiện khả năng phân tích bài toán, cài đặt giải pháp
tối ưu truy vấn SQL, đồng thời đánh giá hiệu quả giữa hai cách tiếp cận – cơ bản và nâng cao. Để có thể
thực hiện việc lập trình và đánh giá hiệu năng của SQL nâng cao, một yếu tố quan trọng là hiểu rõ về cú
pháp, cấu trúc truy vấn, kỹ thuật tối ưu và các công cụ hỗ trợ phân tích hiệu năng trong các hệ quản trị
cơ sở dữ liệu phổ biến như SQL Server.
Cấu trúc đồ án
Từ động lực trên, tác giả chia đồ án thành các chương, mỗi chương trình bày các nội dung cụ thể
như sau:
1. Chương 1: Giới thiệu tổng quan về bộ dữ liệu sử dụng, mối quan hệ giữa các thực thể.
lOMoARcPSD| 45315597
MỤC LỤC
2. Chương 2: Nêu các bài toán cụ thể, cách tiếp cận và lời giải sử dụng các kỹ thuật SQL nâng cao.
Chạy thử nghiệm và đánh giá kết quả.
3. Chương 3: Đề xuất các hướng mở rộng và phát triển trong tương lai.
lOMoARcPSD|45315597
1 MÔ TẢ VỀ BỘ DỮ LIỆU
1 Mô tả về bộ dữ liệu
Cơ sở dữ liệu được thiết kế để quản lý thông tin về lớp học, môn học, sinh viên, giảng viên và các
mối quan hệ liên quan trong môi trường học thuật. Dưới đây là mô tả chi tiết cho từng bảng:
1. Bảng classroom
• Lưu thông tin phòng học.
• Các cột:
– building: tên tòa nhà.
– room_number: số phòng.
– capacity: sức chứa phòng học.
• Khóa chính: (building, room_number).
2. Bảng department
• Lưu thông tin khoa/viện.
• Các cột: dept_name, building, budget.
• Khóa chính: dept_name.
• Ràng buộc: budget > 0.
3. Bảng course
• Quản lý các môn học.
• Các cột: course_id, title, dept_name, credits.
• Khóa chính: course_id.
• Khóa ngoại: dept_name tham chiếu department.
4. Bảng instructor
• Lưu thông tin giảng viên.
• Các cột: ID, name, dept_name, salary.
• Khóa chính: ID.
• Khóa ngoại: dept_name tham chiếu department.
• Ràng buộc: salary > 29000.
lOMoARcPSD| 45315597
1 MÔ TẢ VỀ BỘ DỮ LIỆU
5. Bảng section
• Lưu thông tin các lớp học phần.
• Các cột: course_id, sec_id, semester, year, building, room_number, time_slot_id.
• Khóa chính: (course_id, sec_id, semester, year).
• Khóa ngoại:
– course_id→course.
– (building, room_number) →classroom.
• Ràng buộc: giá trị semester phải thuộc {Fall, Winter, Spring, Summer}.
6. Bảng teaches
• Xác định mối quan hệ giữa giảng viên và lớp học phần mà họ dạy.
• Các cột: ID, course_id, sec_id, semester, year.
• Khóa chính: (ID, course_id, sec_id, semester, year).
• Khóa ngoại:
– (course_id, sec_id, semester, year) →section.
– ID→instructor.
7. Bảng student
• Lưu thông tin sinh viên.
• Các cột: ID, name, dept_name, tot_cred.
• Khóa chính: ID.
• Khóa ngoại: dept_name→department.
• Ràng buộc: tot_cred≥ 0.
8. Bảng takes
• Ghi nhận thông tin sinh viên đăng ký học phần.
• Các cột: ID, course_id, sec_id, semester, year, grade.
• Khóa chính: (ID, course_id, sec_id, semester, year).
• Khóa ngoại:
lOMoARcPSD| 45315597
1 MÔ TẢ VỀ BỘ DỮ LIỆU
– (course_id, sec_id, semester, year) →section.
– ID→student.
9. Bảng advisor
• Quan hệ giữa sinh viên và giảng viên cố vấn.
• Các cột: s_ID, i_ID.
• Khóa chính: s_ID.
• Khóa ngoại:
– s_ID→student.
– i_ID→instructor.
10. Bảng time_slot
• Lưu thời gian học của các lớp.
• Các cột: time_slot_id, day, start_hr, start_min, end_hr, end_min.
• Khóa chính: (time_slot_id, day, start_hr, start_min).
11. Bảng prereq
• Lưu thông tin môn học tiên quyết.
• Các cột: course_id, prereq_id.
• Khóa chính: (course_id, prereq_id).
• Khóa ngoại:
– course_id→course.
– prereq_id→course.
Tóm tắt:
• Cơ sở dữ liệu bao gồm 11 bảng chính, đảm bảo tính toàn vẹn dữ liệu thông qua các khóa chính và
khóa ngoại.
• Hỗ trợ đầy đủ cho việc quản lý sinh viên, giảng viên, khóa học, phòng học, thời gian học và mối
quan hệ tiên quyết giữa các môn.
lOMoARcPSD|45315597
2
BÀI TẬP
2 Bài tập
2.1 Bài tập 1
Kiểm tra 1 sinh viên đã đủ điều kiện tốt nghiệp chưa biết rằng các điều kiện để một sinh viên tốt
nghiệp là:
1. Tích lũy đủ số tín chỉ
2. Điểm phẩy tốt nghiệp không nhỏ hơn 1.0, biết bảng đổi điểm như sau:
Bảng 1: Thang điểm đánh giá kết quả học tập
Thang điểm 10
Thang điểm 4
(Điểm thành phần)
Điểm chữ
Điểm số
Đạt*
từ 9,5 đến 10
A+
4,5
từ 8,5 đến 9,4
A
4,0
từ 8,0 đến 8,4
B+
3,5
từ 7,0 đến 7,9
B
3,0
từ 6,5 đến 6,9
C+
2,5
từ 5,5 đến 6,4
C
2,0
từ 5,0 đến 5,4
D+
1,5
từ 4,0 đến 4,9
D
1,0
Không đạt
dưới 4,0
F
0
Phương án giải quyết
Để kiểm tra một sinh viên đã đủ điều kiện tốt nghiệp hay chưa, cần đồng thời thỏa mãn hai điều
kiện:
1. Tích lũy đủ số tín chỉ (tot_cred≥ 120).
2. Điểm phẩy tốt nghiệp (GPA) ≥ 1.0, với GPA được tính dựa trên bảng quy đổi điểm chữ sang điểm
số như sau:
Điểm chữ
A+
A
B+
B
C+
C
D+
D
F
lOMoARcPSD|45315597
2
BÀI TẬP
Điểm số
4.5
4.0
3.5
3.0
2.5
2.0
1.5
1.0
0.0
Từ đây ta có cánh triển khai lời giải cho bài toán trên như sau:
Bước 1: Tính GPA của sinh viên
• Sử dụng CTE (Common Table Expression) để tổng hợp dữ liệu của từng sinh viên.
• Kết hợp ba bảng student, takes, course.
• Với mỗi môn học, tính giá trị credits×điểm số (điểm số được quy đổi từ grade).
• GPA được tính theo công thức:
P
(credits × điểm số)
GPA =
P
credits
Sử dụng hàm NULLIF để tránh chia cho 0.
Bước 2: Kiểm tra điều kiện tốt nghiệp
• Sau khi có GPA của từng sinh viên, sử dụng mệnh đề CASE để kiểm tra:
– Nếu tot_cred≥ 120 và GPA≥ 1.0 → Pass graduation condition.
– Ngược lại → Fail graduation condition.
Bước 3: Kết quả cuối cùng
• Câu lệnh SELECT từ GPA_CTE trả về các thông tin:
ID, name, tot_cred, GPA, Graduation_Status.
• Giúp xác định sinh viên đã đạt điều kiện tốt nghiệp hay chưa.
Listing 1: Script SQL cho bài 1
WITH GPA_CTE AS (
SELECT
s.ID,
lOMoARcPSD|45315597
2
BÀI TẬP
s.name,
s.tot_cred, CAST(SUM(c.credits *
CASE t.grade
WHEN ’A+’ THEN 4.5
WHEN ’A’ THEN 4.0
WHEN ’B+’ THEN 3.5
WHEN ’B’ THEN 3.0
WHEN ’C+’ THEN 2.5
WHEN ’C’ THEN 2.0
WHEN ’D+’ THEN 1.5
WHEN ’D’ THEN 1.0
WHEN ’F’ THEN 0.0
END
) AS FLOAT) / NULLIF(SUM(c.credits), 0) AS GPA
FROM student s
JOIN takes t ON s.ID = t.ID
JOIN course c ON t.course_id = c.course_id
GROUP BY s.ID, s.name, s.tot_cred
)
SELECT ID,
name,
tot_cred,
GPA,
CASE
WHEN tot_cred >= 120 AND GPA >= 1.0
THEN N’Pass graduation condition’
ELSE N’Fail graduation condition’
END AS Graduation_Status
FROM GPA_CTE;
Khi chạy script SQL của bài 1, hệ thống đã tính toán GPA (điểm trung bình tích lũy) của từng sinh
viên và kiểm tra điều kiện tốt nghiệp:
• Điều kiện: Sinh viên cần tích lũy đủ số tín chỉ (tot_cred 120) và GPA không nhỏ hơn 1.0.
• Kết quả: Bảng kết quả trả về các cột ID, name, tot_cred, GPA, và Graduation_Status.
Trong kết quả hiển thị, có các bản ghi của các sinh viên đều không đạt điều kiện (Fail graduation condition)
vì chưa đủ tín chỉ hoặc GPA chưa đạt ngưỡng yêu cầu, và các sinh viên đạt điều kiện. Script hoạt động
chính xác, tính đúng GPA và đánh giá trạng thái tốt nghiệp cho 2000 sinh viên.
lOMoARcPSD|45315597
2
BÀI TẬP
2.2 Bài tập 2
Viết thủ tục SP_LOC_DU_LIEU cho phép nhập vào tên trường bất kỳ và một giá trị của trường (Ví dụ:
SP_LOC_DU_LIEU ‘dept_name’, ‘Physics’ ). Kết quả trả về là dữ liệu sau khi lọc theo giá trị của trường dữ
liệu đó. Bảng kết quả trả về gồm các trường: Mã sinh viên, Họ tên sinh
viên, Năm học, Kỳ học, Khóa học, Thời gian học, Phòng học, Giảng viên, Khoa viện.
Phương án giải quyết
Listing 2: Script SQL cho bài 2
Hình 1: Kết quả chạy câu lệnh bài 1
-- DROP EXISTING PROCEDURE IF EXIST FIRST
DROP PROCEDURE IF EXISTS SP_LOC_DU_LIEU
Go
-- CREATE PROCEDURE
CREATE PROCEDURE SP_LOC_DU_LIEU
@fieldName NVARCHAR(100),
@fieldValue NVARCHAR(50)
AS
BEGIN
DECLARE @sql NVARCHAR(MAX);
DECLARE @column NVARCHAR(100);
SET @column = @fieldName;
lOMoARcPSD| 45315597
2
BÀI TẬP
SET @column = REPLACE(@column, ’student.’, ’s.’);
SET @column = REPLACE(@column, ’takes.’, ’t.’);
SET @column = REPLACE(@column, ’section.’, ’sec.’); SET @column =
REPLACE(@column, ’course.’, ’c.’);
SET @column = REPLACE(@column, ’time_slot.’, ’ts.’);
SET @column = REPLACE(@column, ’classroom.’, ’cl.’);
SET @column = REPLACE(@column, ’teaches.’, ’te.’);
SET @column = REPLACE(@column, ’instructor.’, ’i.’);
SET @column = REPLACE(@column, ’department.’, ’d.’);
IF @column NOT LIKE ’[a-z].[a-z]%’
BEGIN
RAISERROR(’Invalid field name format. Use TableName.ColumnName (e.g., department.dept_name)’,
16, 1);
RETURN;
END;
SET @sql = ’
SELECT
s.ID AS StudentID,
s.name AS StudentName, sec.year AS
AcademicYear, sec.semester AS
Semester, c.title AS CourseTitle,
CONCAT(’’From ’’, ts.start_hr, ’’:’’, ts.start_min, ’’ to ’’, ts.end_hr,
’’:’’, ts.end_min) AS ClassTime,
CONCAT(sec.building, ’’ ’’, sec.room_number) AS Classroom, i.name AS
InstructorName,
d.dept_name AS DepartmentName
FROM student s
JOIN takes t ON s.ID = t.ID
JOIN section sec ON t.course_id = sec.course_id
AND t.sec_id = sec.sec_id
AND t.semester = sec.semester
AND t.year = sec.year
JOIN course c ON sec.course_id = c.course_id
LEFT JOIN time_slot ts ON sec.time_slot_id = ts.time_slot_id
LEFT JOIN classroom cl ON sec.building = cl.building
AND sec.room_number = cl.room_number
LEFT JOIN teaches te ON sec.course_id = te.course_id
AND sec.sec_id = te.sec_id
AND sec.semester = te.semester
AND sec.year = te.year
lOMoARcPSD|45315597
2
BÀI TẬP
LEFT JOIN instructor i ON te.ID = i.ID
LEFT JOIN department d ON s.dept_name = d.dept_name
WHERE ’ + @column + ’ = @fieldValue
’;
EXEC sp_executesql @sql, N’@fieldValue NVARCHAR(50)’, @fieldValue;
END;
GO
Mục tiêu của bài toán là viết một thủ tục SP_LOC_DU_LIEU cho phép truyền vào tên trường dữ liệu
bất kỳ và giá trị cần lọc, từ đó trả về danh sách thông tin sinh viên và khóa học tương ứng. Thủ tục được
thiết kế để xử lý linh hoạt các tên trường thuộc nhiều bảng khác nhau. Cách tiếp cận của lời giải như sau:
1. Định nghĩa tham số đầu vào: Thủ tục nhận hai tham số:
• @fieldName: tên trường dữ liệu cần lọc, bao gồm cả tên bảng (ví dụ: student.id).
• @fieldValue: giá trị cần lọc.
2. Xử lý ánh xạ tên bảng:
• Do truy vấn sử dụng bí danh bảng (s, t, sec, c, ts, cl, te, i, d), nên thủ tục cần ánh xạ tên bảng
gốc (ví dụ: student) sang bí danh tương ứng (s).
• Thực hiện REPLACE tuần tự để thay đổi tiền tố bảng trong tên trường.
3. Kiểm tra định dạng tên trường:
• Đảm bảo tham số @fieldName sau khi ánh xạ có định dạng hợp lệ (alias.column), tránh lỗi
hoặc tấn công SQL Injection.
• Nếu không hợp lệ, thông báo lỗi qua RAISERROR.
4. Xây dựng câu lệnh SQL động:
• Viết câu truy vấn với đầy đủ các bảng liên quan: student, takes, section, course, time_slot,
classroom, teaches, instructor, department.
• Chỉ định các cột cần trả về: Mã sinh viên, Họ tên, Năm học, Kỳ học, Khóa học, Thời gian học,
Phòng học, Giảng viên, Khoa viện.
• Phần điều kiện WHERE được ghép động bằng tham số @column.
5. Thực thi câu lệnh động:
lOMoARcPSD|45315597
2
BÀI TẬP
• Sử dụng sp_executesql để thực thi câu SQL động, truyền tham số giá trị cần lọc (@fieldValue)
một cách an toàn.
6. Kết quả: • Thủ tục trả về danh sách dữ liệu sinh viên cùng các thông tin khóa học sau khi lọc đúng theo tên
trường và giá trị nhập vào.
Để chạy thủ tục trên, ta thực hiện câu lệnh, truyền vào các tham số phù hợp
EXEC SP_LOC_DU_LIEU ’student.id’, ’19824’;
Kết quả hệ thống trả về đã lọc và trả về danh sách toàn bộ các học phần mà sinh viên này đã đăng
ký:
• Thông tin hiển thị: Bao gồm StudentID, StudentName, AcademicYear, Semester, CourseTitle, thời
gian học (ClassTime), phòng học (Classroom), giảng viên (InstructorName), và khoa
(DepartmentName).
• Kết quả: Sinh viên Saveileva có mã số 19824 đã tham gia rất nhiều học phần trong nhiều học kỳ
khác nhau (Fall, Spring) và các năm từ 2003 đến 2010. • Mục đích: Giúp nhanh chóng truy vấn
thông tin lịch sử học tập của một sinh viên cụ thể, hỗ trợ kiểm tra quá trình đăng ký môn và quá
trình học tập.
Để kiểm thử chương trình đã tạo, ta thực hiện câu lệnh:
Hình 2: Kết quả chạy câu lệnh bài 2
lOMoARcPSD|45315597
2
BÀI TẬP
DECLARE @tbl FilterTable;
INSERT INTO @tbl (FieldName, FieldValue) VALUES
(’student.name’, ’Manber’),
(’course.title’, ’The Music of the Ramones’);
EXEC SP_LOC_DU_LIEU @tbl;
Kết quả trả về là danh sách các lần đăng ký học phần của sinh viên Manber cho môn The Music of
the Ramones, bao gồm các thông tin chi tiết:
• StudentID, StudentName – Mã và tên sinh viên.
• AcademicYear, Semester – Năm học và học kỳ.
• CourseTitle – Tên môn học.
• ClassTime, Classroom – Thời gian và phòng học.
• InstructorName, DepartmentName – Giảng viên và khoa giảng dạy.
2.3 Bài tập 3
Viết thủ tục SP_LOC_DU_LIEU cho phép nhập vào một biến kiểu table gồm 2 trường: tên trường và
một giá trị của trường. Kết quả trả về là dữ liệu sau khi lọc theo danh sách các giá trị của các trường dữ
liệu đó.
Bảng kết quả trả về gồm các trường: Mã sinh viên, Họ tên sinh viên, Năm học, Kỳ học, Khóa học, Thời
gian học, Phòng học, Giảng viên, Khoa viện.
Phương án giải quyết
Thủ tục SP_LOC_DU_LIEU được xây dựng để hỗ trợ lọc dữ liệu động với nhiều điều kiện cùng lúc,
dựa trên danh sách các cặp (Tên trường, Giá trị) được truyền vào dưới dạng table-valued parameter.
Giải pháp được triển khai theo các bước chính:
1. Định nghĩa kiểu bảng FilterTable
• Kiểu bảng FilterTable gồm hai cột:
– FieldName: tên trường dữ liệu cần lọc (bao gồm tên bảng), ví dụ student.name.
lOMoARcPSD|45315597
2
BÀI TẬP
– FieldValue: giá trị cần lọc.
• Cấu trúc này cho phép người dùng truyền nhiều điều kiện lọc vào cùng một lần gọi thủ tục.
2. Ánh xạ tên bảng sang bí danh (alias)
• Trong câu truy vấn chính, các bảng sử dụng alias như s, t, sec, c, ts, cl, te, i, d.
• Thủ tục thực hiện ánh xạ FieldName từ tên bảng gốc sang alias tương ứng bằng cách dùng
chuỗi các lệnh REPLACE cho từng bản ghi trong @filters.
3. Xây dựng điều kiện WHERE động
• Sử dụng hàm STRING_AGG để kết hợp các điều kiện lọc (ví dụ: s.name = ’John’ AND
c.title = ’Database’) thành một chuỗi duy nhất.
• Cách làm này giúp thủ tục xử lý được bất kỳ số lượng điều kiện lọc nào.
4. Tạo và thực thi câu truy vấn động • Câu truy vấn chính trả về danh sách thông tin sinh viên và khóa học
gồm: Mã sinh viên, Họ tên, Năm học, Kỳ học, Khóa học, Thời gian học, Phòng học, Giảng viên, Khoa viện.
• Phần điều kiện WHERE được ghép động từ chuỗi điều kiện đã xây dựng và được thực thi bằng
sp_executesql.
5. Kết quả trả về
• Dữ liệu sau khi lọc được trả về chính xác theo các trường và giá trị truyền vào.
• Hỗ trợ lọc đồng thời nhiều trường khác nhau, ví dụ: lọc theo tên sinh viên và tên khóa học
cùng lúc.
Listing 3: Script SQL cho bài 3
CREATE TYPE FilterTable AS TABLE
(
FieldName NVARCHAR(100), -- Example: student.name, course.title FieldValue
NVARCHAR(100)
);
GO
DROP PROCEDURE IF EXISTS SP_LOC_DU_LIEU;
GO
lOMoARcPSD| 45315597
2
BÀI TẬP
CREATE PROCEDURE SP_LOC_DU_LIEU
@filters FilterTable READONLY
AS
BEGIN
DECLARE @sql NVARCHAR(MAX) = ’’;
DECLARE @where NVARCHAR(MAX) = ’’;
SELECT @where = STRING_AGG(mapped.ColumnName + ’ = ’’’ + f.FieldValue + ’’’’, ’ AND
’)
FROM @filters f
CROSS APPLY (
SELECT
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(f.FieldName,
’student.’, ’s.’),
’takes.’, ’t.’),
’section.’, ’sec.’),
’course.’, ’c.’),
’time_slot.’, ’ts.’),
’classroom.’, ’cl.’),
’teaches.’, ’te.’),
’instructor.’, ’i.’),
’department.’, ’d.’
) AS ColumnName
) mapped;
-- Build final SQL
SET @sql = ’
SELECT
s.ID AS StudentID,
s.name AS StudentName, sec.year AS
AcademicYear, sec.semester AS
Semester, c.title AS CourseTitle,
CONCAT(’’From ’’, ts.start_hr, ’’:’’, ts.start_min, ’’ to ’’, ts.end_hr,
’’:’’, ts.end_min) AS ClassTime,
lOMoARcPSD|45315597
2
BÀI TẬP
CONCAT(sec.building, ’’ ’’, sec.room_number) AS Classroom, i.name AS
InstructorName,
d.dept_name AS DepartmentName
FROM student s
JOIN takes t ON s.ID = t.ID
JOIN section sec ON t.course_id = sec.course_id
AND t.sec_id = sec.sec_id
AND t.semester = sec.semester
AND t.year = sec.year
JOIN course c ON sec.course_id = c.course_id
LEFT JOIN time_slot ts ON sec.time_slot_id = ts.time_slot_id
LEFT JOIN classroom cl ON sec.building = cl.building
AND sec.room_number = cl.room_number
LEFT JOIN teaches te ON sec.course_id = te.course_id
AND sec.sec_id = te.sec_id
AND sec.semester = te.semester
AND sec.year = te.year
LEFT JOIN instructor i ON te.ID = i.ID
LEFT JOIN department d ON s.dept_name = d.dept_name
’
+ CASE WHEN @where IS NOT NULL AND @where <> ’’ THEN ’ WHERE ’ + @where ELSE ’’
END;
EXEC sp_executesql @sql;
END;
GO
2.4 Bài tập 4
Sinh viên A muốn học môn ‘Mobile Computing’ hỏi A cần phải học qua những môn gì?
Phương án giải quyết
Mục tiêu của bài toán là tìm danh sách tất cả các môn học tiên quyết (prerequisite) cho một môn
học cụ thể (ví dụ: “The Music of the Ramones”). Giải pháp sử dụng CTE đệ quy (Common Table
Expression) để truy xuất cả các môn học tiên quyết trực tiếp và gián tiếp. Các bước thực hiện:
1. Định nghĩa CTE prereq_chain gồm hai phần:
lOMoARcPSD|45315597
2
BÀI TẬP
Hình 3: Kết quả chạy câu lệnh bài 3
• Truy vấn cơ sở (base query): Lấy các môn học tiên quyết trực tiếp bằng cách:
– Kết nối bảng course với prereq.
– Lọc theo môn học mục tiêu (ví dụ: title = ’The Music of the Ramones’).
• Truy vấn đệ quy (recursive query): Lấy các môn học tiên quyết của những môn đã tìm được ở
cấp trước đó (tức là tìm các điều kiện tiên quyết nhiều cấp).
2. Cơ chế đệ quy:
• Kết hợp kết quả từ cấp trước (prereq_chain) với bảng prereq.
• Liên kết thêm với bảng course để lấy tên môn học tiên quyết.
• Mỗi vòng lặp sẽ mở rộng danh sách các môn học tiên quyết lên một cấp.
3. Kết quả cuối cùng:
• Sử dụng SELECT DISTINCT để loại bỏ trùng lặp.
• Trả về mã môn học tiên quyết (MaMonTienQuyet) và tên môn tiên quyết (TenMonTienQuyet)
cho môn học đã chọn.
Phương pháp này đảm bảo rằng tất cả các môn học tiên quyết, bao gồm cả tiên quyết nhiều cấp, đều
được liệt kê đầy đủ trong kết quả.
Listing 4: Script SQL cho bài 3

Tài liệu khác của Đại học Bách Khoa Hà Nội

Preview text:

lOMoAR cPSD| 45315597 M lOMoARcPSD| 45315597 MỤC LỤC Mục lục
1 Mô tả về bộ dữ liệu .............................................................................................................................................. 5
2 Bài tập .................................................................................................................................................................... 8
2.1 Bài tập 1 .............................................................................................................................................................. 8
2.2 Bài tập 2 ............................................................................................................................................................ 11
2.3 Bài tập 3 ............................................................................................................................................................ 15
2.4 Bài tập 4 ............................................................................................................................................................ 18
2.5 Bài tập 5 ............................................................................................................................................................ 21
2.6 Bài tập 6 ............................................................................................................................................................ 23
2.7 Bài tập 7 ............................................................................................................................................................ 26
2.8 Bài tập 8 ............................................................................................................................................................ 30
2.9 Bài tập 9 ............................................................................................................................................................ 33
2.10 Bài tập 10........................................................................................................................................................ 36
3 Kết luận và các hướng phát triển ..................................................................................................................... 41 lOMoAR cPSD| 45315597 MỤC LỤC Lời cảm ơn
Trong học kỳ này, tác giả đã có cơ hội học tập môn Cơ sở dữ liệu nâng cao, một học phần có tính
ứng dụng cao và mang lại nhiều giá trị thực tiễn trong lĩnh vực nghiên cứu và kỹ thuật. Xin trân trọng
cảm ơn TS. Trần Ngọc Thăng, giảng viên phụ trách môn học, vì sự hướng dẫn tận tình và định hướng
chuyên môn rõ ràng trong suốt quá trình học cũng như khi thực hiện đề tài báo cáo.
Những nội dung được truyền đạt trong môn học, cùng với phương pháp giảng dạy rõ ràng, logic và
có hệ thống của thầy, đã giúp chúng em tiếp cận hiệu quả với các khái niệm và kỹ thuật lập trình song
song vốn có tính trừu tượng cao. Các gợi ý chuyên môn từ thầy đã hỗ trợ đáng kể trong việc hoàn thiện báo cáo này.
Dù đã nỗ lực thực hiện một cách nghiêm túc, song bài báo cáo vẫn có thể tồn tại một số hạn chế
và lỗi sai sót. Rất mong nhận được ý kiến đóng góp từ thầy để có thể cải thiện và nâng cao chất lượng báo cáo trong tương lai. Xin trân trọng cảm ơn. lOMoAR cPSD| 45315597 MỤC LỤC Lời mở đầu
Trong bối cảnh thế giới ngày nay, sự phát triển bùng nổ của ngành công nghệ thông tin cùng với
nhu cầu khoa học kỹ thuật áp dụng cho đời sống đã khiến lượng dữ liệu sinh ra và cần xử lý tăng lên
theo cấp số nhân từng giờ từng ngày. Đi kèm với sự bùng nổ về mặt dữ liệu, hiệu suất truy vấn và xử lý
dữ liệu ngày càng trở nên quan trọng. Đặc biệt, trong các hệ thống cơ sở dữ liệu lớn hoặc yêu cầu thời
gian thực, việc áp dụng các kỹ thuật tối ưu truy vấn SQL nâng cao là điều tất yếu.
Việc tối ưu câu lệnh SQL không chỉ giúp tăng tốc độ truy vấn dữ liệu mà còn mở rộng khả năng xử
lý các bài toán phức tạp, tận dụng được sức mạnh của các hệ quản trị cơ sở dữ liệu hiện đại. Trong đồ
án này, tác giả xin trình bày cách áp dụng các kỹ thuật SQL nâng cao để giải quyết các bài toán cụ thể ,
qua đó thể hiện sức mạnh của tối ưu truy vấn và nâng cao hiệu suất xử lý dữ liệu. Việc áp dụng các kỹ
thuật SQL nâng cao như subquery, window functions, indexing, optimization hints sẽ mang lại hiệu quả
đáng kể khi dữ liệu mở rộng (bảng dữ liệu lớn).
Thông qua đồ án này, tác giả mong muốn thể hiện khả năng phân tích bài toán, cài đặt giải pháp
tối ưu truy vấn SQL, đồng thời đánh giá hiệu quả giữa hai cách tiếp cận – cơ bản và nâng cao. Để có thể
thực hiện việc lập trình và đánh giá hiệu năng của SQL nâng cao, một yếu tố quan trọng là hiểu rõ về cú
pháp, cấu trúc truy vấn, kỹ thuật tối ưu và các công cụ hỗ trợ phân tích hiệu năng trong các hệ quản trị
cơ sở dữ liệu phổ biến như SQL Server. Cấu trúc đồ án
Từ động lực trên, tác giả chia đồ án thành các chương, mỗi chương trình bày các nội dung cụ thể như sau:
1. Chương 1: Giới thiệu tổng quan về bộ dữ liệu sử dụng, mối quan hệ giữa các thực thể. lOMoAR cPSD| 45315597 MỤC LỤC
2. Chương 2: Nêu các bài toán cụ thể, cách tiếp cận và lời giải sử dụng các kỹ thuật SQL nâng cao.
Chạy thử nghiệm và đánh giá kết quả.
3. Chương 3: Đề xuất các hướng mở rộng và phát triển trong tương lai. lOMoARcPSD| 45315597 1
MÔ TẢ VỀ BỘ DỮ LIỆU
1 Mô tả về bộ dữ liệu
Cơ sở dữ liệu được thiết kế để quản lý thông tin về lớp học, môn học, sinh viên, giảng viên và các
mối quan hệ liên quan trong môi trường học thuật. Dưới đây là mô tả chi tiết cho từng bảng: 1. Bảng classroom
• Lưu thông tin phòng học. • Các cột: – building: tên tòa nhà. – room_number: số phòng.
– capacity: sức chứa phòng học.
• Khóa chính: (building, room_number). 2. Bảng department
• Lưu thông tin khoa/viện.
• Các cột: dept_name, building, budget. • Khóa chính: dept_name.
• Ràng buộc: budget > 0. 3. Bảng course
• Quản lý các môn học.
• Các cột: course_id, title, dept_name, credits. • Khóa chính: course_id.
• Khóa ngoại: dept_name tham chiếu department. 4. Bảng instructor
• Lưu thông tin giảng viên.
• Các cột: ID, name, dept_name, salary. • Khóa chính: ID.
• Khóa ngoại: dept_name tham chiếu department.
• Ràng buộc: salary > 29000. lOMoAR cPSD| 45315597 1
MÔ TẢ VỀ BỘ DỮ LIỆU 5. Bảng section
• Lưu thông tin các lớp học phần.
• Các cột: course_id, sec_id, semester, year, building, room_number, time_slot_id.
• Khóa chính: (course_id, sec_id, semester, year). • Khóa ngoại: – course_id→course.
– (building, room_number) →classroom.
• Ràng buộc: giá trị semester phải thuộc {Fall, Winter, Spring, Summer}. 6. Bảng teaches
• Xác định mối quan hệ giữa giảng viên và lớp học phần mà họ dạy.
• Các cột: ID, course_id, sec_id, semester, year.
• Khóa chính: (ID, course_id, sec_id, semester, year). • Khóa ngoại:
– (course_id, sec_id, semester, year) →section. – ID→instructor. 7. Bảng student
• Lưu thông tin sinh viên.
• Các cột: ID, name, dept_name, tot_cred. • Khóa chính: ID.
• Khóa ngoại: dept_name→department.
• Ràng buộc: tot_cred≥ 0. 8. Bảng takes
• Ghi nhận thông tin sinh viên đăng ký học phần.
• Các cột: ID, course_id, sec_id, semester, year, grade.
• Khóa chính: (ID, course_id, sec_id, semester, year). • Khóa ngoại: lOMoAR cPSD| 45315597 1
MÔ TẢ VỀ BỘ DỮ LIỆU
– (course_id, sec_id, semester, year) →section. – ID→student. 9. Bảng advisor
• Quan hệ giữa sinh viên và giảng viên cố vấn. • Các cột: s_ID, i_ID. • Khóa chính: s_ID. • Khóa ngoại: – s_ID→student. – i_ID→instructor. 10. Bảng time_slot
• Lưu thời gian học của các lớp.
• Các cột: time_slot_id, day, start_hr, start_min, end_hr, end_min.
• Khóa chính: (time_slot_id, day, start_hr, start_min). 11. Bảng prereq
• Lưu thông tin môn học tiên quyết.
• Các cột: course_id, prereq_id.
• Khóa chính: (course_id, prereq_id). • Khóa ngoại: – course_id→course. – prereq_id→course. Tóm tắt:
• Cơ sở dữ liệu bao gồm 11 bảng chính, đảm bảo tính toàn vẹn dữ liệu thông qua các khóa chính và khóa ngoại.
• Hỗ trợ đầy đủ cho việc quản lý sinh viên, giảng viên, khóa học, phòng học, thời gian học và mối
quan hệ tiên quyết giữa các môn. lOMoARcPSD| 45315597 2 BÀI TẬP 2 Bài tập 2.1 Bài tập 1
Kiểm tra 1 sinh viên đã đủ điều kiện tốt nghiệp chưa biết rằng các điều kiện để một sinh viên tốt nghiệp là:
1. Tích lũy đủ số tín chỉ
2. Điểm phẩy tốt nghiệp không nhỏ hơn 1.0, biết bảng đổi điểm như sau:
Bảng 1: Thang điểm đánh giá kết quả học tập Thang điểm 10 Thang điểm 4 (Điểm thành phần) Điểm chữ Điểm số từ 9,5 đến 10 A+ 4,5 từ 8,5 đến 9,4 A 4,0 từ 8,0 đến 8,4 B+ 3,5 từ 7,0 đến 7,9 B 3,0 Đạt* từ 6,5 đến 6,9 C+ 2,5 từ 5,5 đến 6,4 C 2,0 từ 5,0 đến 5,4 D+ 1,5 từ 4,0 đến 4,9 D 1,0 Không đạt dưới 4,0 F 0
Phương án giải quyết
Để kiểm tra một sinh viên đã đủ điều kiện tốt nghiệp hay chưa, cần đồng thời thỏa mãn hai điều kiện:
1. Tích lũy đủ số tín chỉ (tot_cred≥ 120).
2. Điểm phẩy tốt nghiệp (GPA) ≥ 1.0, với GPA được tính dựa trên bảng quy đổi điểm chữ sang điểm số như sau: Điểm chữ A+ A B+ B C+ C D+ D F lOMoARcPSD| 45315597 2 BÀI TẬP Điểm số
4.5 4.0 3.5 3.0 2.5 2.0 1.5 1.0 0.0
Từ đây ta có cánh triển khai lời giải cho bài toán trên như sau:
Bước 1: Tính GPA của sinh viên
• Sử dụng CTE (Common Table Expression) để tổng hợp dữ liệu của từng sinh viên.
• Kết hợp ba bảng student, takes, course.
• Với mỗi môn học, tính giá trị credits×điểm số (điểm số được quy đổi từ grade).
• GPA được tính theo công thức: P(credits × điểm số) GPA = Pcredits
Sử dụng hàm NULLIF để tránh chia cho 0.
Bước 2: Kiểm tra điều kiện tốt nghiệp
• Sau khi có GPA của từng sinh viên, sử dụng mệnh đề CASE để kiểm tra:
– Nếu tot_cred≥ 120 và GPA≥ 1.0 → Pass graduation condition.
– Ngược lại → Fail graduation condition.
Bước 3: Kết quả cuối cùng
• Câu lệnh SELECT từ GPA_CTE trả về các thông tin:
ID, name, tot_cred, GPA, Graduation_Status.
• Giúp xác định sinh viên đã đạt điều kiện tốt nghiệp hay chưa.
Listing 1: Script SQL cho bài 1 WITH GPA_CTE AS ( SELECT s.ID, lOMoARcPSD| 45315597 2 BÀI TẬP s.name,
s.tot_cred, CAST(SUM(c.credits * CASE t.grade WHEN ’A+’ THEN 4.5 WHEN ’A’ THEN 4.0 WHEN ’B+’ THEN 3.5 WHEN ’B’ THEN 3.0 WHEN ’C+’ THEN 2.5 WHEN ’C’ THEN 2.0 WHEN ’D+’ THEN 1.5 WHEN ’D’ THEN 1.0 WHEN ’F’ THEN 0.0 END
) AS FLOAT) / NULLIF(SUM(c.credits), 0) AS GPA FROM student s JOIN takes t ON s.ID = t.ID
JOIN course c ON t.course_id = c.course_id
GROUP BY s.ID, s.name, s.tot_cred ) SELECT ID, name, tot_cred, GPA, CASE
WHEN tot_cred >= 120 AND GPA >= 1.0
THEN N’Pass graduation condition’
ELSE N’Fail graduation condition’ END AS Graduation_Status FROM GPA_CTE;
Khi chạy script SQL của bài 1, hệ thống đã tính toán GPA (điểm trung bình tích lũy) của từng sinh
viên và kiểm tra điều kiện tốt nghiệp:
• Điều kiện: Sinh viên cần tích lũy đủ số tín chỉ (tot_cred 120) và GPA không nhỏ hơn 1.0.
• Kết quả: Bảng kết quả trả về các cột ID, name, tot_cred, GPA, và Graduation_Status.
Trong kết quả hiển thị, có các bản ghi của các sinh viên đều không đạt điều kiện (Fail graduation condition)
vì chưa đủ tín chỉ hoặc GPA chưa đạt ngưỡng yêu cầu, và các sinh viên đạt điều kiện. Script hoạt động
chính xác, tính đúng GPA và đánh giá trạng thái tốt nghiệp cho 2000 sinh viên. lOMoARcPSD| 45315597 2 BÀI TẬP 2.2 Bài tập 2
Viết thủ tục SP_LOC_DU_LIEU cho phép nhập vào tên trường bất kỳ và một giá trị của trường (Ví dụ:
SP_LOC_DU_LIEU ‘dept_name’, ‘Physics’ ). Kết quả trả về là dữ liệu sau khi lọc theo giá trị của trường dữ
liệu đó. Bảng kết quả trả về gồm các trường: Mã sinh viên, Họ tên sinh
viên, Năm học, Kỳ học, Khóa học, Thời gian học, Phòng học, Giảng viên, Khoa viện.
Phương án giải quyết
Listing 2: Script SQL cho bài 2
Hình 1: Kết quả chạy câu lệnh bài 1
-- DROP EXISTING PROCEDURE IF EXIST FIRST
DROP PROCEDURE IF EXISTS SP_LOC_DU_LIEU Go -- CREATE PROCEDURE
CREATE PROCEDURE SP_LOC_DU_LIEU @fieldName NVARCHAR(100), @fieldValue NVARCHAR(50) AS BEGIN DECLARE @sql NVARCHAR(MAX);
DECLARE @column NVARCHAR(100); SET @column = @fieldName; lOMoAR cPSD| 45315597 2 BÀI TẬP
SET @column = REPLACE(@column, ’student.’, ’s.’);
SET @column = REPLACE(@column, ’takes.’, ’t.’);
SET @column = REPLACE(@column, ’section.’, ’sec.’); SET @column =
REPLACE(@column, ’course.’, ’c.’);
SET @column = REPLACE(@column, ’time_slot.’, ’ts.’);
SET @column = REPLACE(@column, ’classroom.’, ’cl.’);
SET @column = REPLACE(@column, ’teaches.’, ’te.’);
SET @column = REPLACE(@column, ’instructor.’, ’i.’);
SET @column = REPLACE(@column, ’department.’, ’d.’);
IF @column NOT LIKE ’[a-z].[a-z]%’ BEGIN
RAISERROR(’Invalid field name format. Use TableName.ColumnName (e.g., department.dept_name)’, 16, 1); RETURN; END; SET @sql = ’ SELECT s.ID AS StudentID,
s.name AS StudentName, sec.year AS AcademicYear, sec.semester AS
Semester, c.title AS CourseTitle,
CONCAT(’’From ’’, ts.start_hr, ’’:’’, ts.start_min, ’’ to ’’, ts.end_hr,
’’:’’, ts.end_min) AS ClassTime,
CONCAT(sec.building, ’’ ’’, sec.room_number) AS Classroom, i.name AS InstructorName, d.dept_name AS DepartmentName FROM student s JOIN takes t ON s.ID = t.ID
JOIN section sec ON t.course_id = sec.course_id AND t.sec_id = sec.sec_id AND t.semester = sec.semester AND t.year = sec.year
JOIN course c ON sec.course_id = c.course_id
LEFT JOIN time_slot ts ON sec.time_slot_id = ts.time_slot_id
LEFT JOIN classroom cl ON sec.building = cl.building
AND sec.room_number = cl.room_number
LEFT JOIN teaches te ON sec.course_id = te.course_id AND sec.sec_id = te.sec_id
AND sec.semester = te.semester AND sec.year = te.year lOMoARcPSD| 45315597 2 BÀI TẬP
LEFT JOIN instructor i ON te.ID = i.ID
LEFT JOIN department d ON s.dept_name = d.dept_name
WHERE ’ + @column + ’ = @fieldValue ’;
EXEC sp_executesql @sql, N’@fieldValue NVARCHAR(50)’, @fieldValue; END; GO
Mục tiêu của bài toán là viết một thủ tục SP_LOC_DU_LIEU cho phép truyền vào tên trường dữ liệu
bất kỳ và giá trị cần lọc, từ đó trả về danh sách thông tin sinh viên và khóa học tương ứng. Thủ tục được
thiết kế để xử lý linh hoạt các tên trường thuộc nhiều bảng khác nhau. Cách tiếp cận của lời giải như sau:
1. Định nghĩa tham số đầu vào: Thủ tục nhận hai tham số:
• @fieldName: tên trường dữ liệu cần lọc, bao gồm cả tên bảng (ví dụ: student.id).
• @fieldValue: giá trị cần lọc.
2. Xử lý ánh xạ tên bảng:
• Do truy vấn sử dụng bí danh bảng (s, t, sec, c, ts, cl, te, i, d), nên thủ tục cần ánh xạ tên bảng
gốc (ví dụ: student) sang bí danh tương ứng (s).
• Thực hiện REPLACE tuần tự để thay đổi tiền tố bảng trong tên trường.
3. Kiểm tra định dạng tên trường:
• Đảm bảo tham số @fieldName sau khi ánh xạ có định dạng hợp lệ (alias.column), tránh lỗi
hoặc tấn công SQL Injection.
• Nếu không hợp lệ, thông báo lỗi qua RAISERROR.
4. Xây dựng câu lệnh SQL động:
• Viết câu truy vấn với đầy đủ các bảng liên quan: student, takes, section, course, time_slot,
classroom, teaches, instructor, department.
• Chỉ định các cột cần trả về: Mã sinh viên, Họ tên, Năm học, Kỳ học, Khóa học, Thời gian học,
Phòng học, Giảng viên, Khoa viện.
• Phần điều kiện WHERE được ghép động bằng tham số @column.
5. Thực thi câu lệnh động: lOMoARcPSD| 45315597 2 BÀI TẬP
• Sử dụng sp_executesql để thực thi câu SQL động, truyền tham số giá trị cần lọc (@fieldValue) một cách an toàn.
6. Kết quả: • Thủ tục trả về danh sách dữ liệu sinh viên cùng các thông tin khóa học sau khi lọc đúng theo tên
trường và giá trị nhập vào.
Để chạy thủ tục trên, ta thực hiện câu lệnh, truyền vào các tham số phù hợp
EXEC SP_LOC_DU_LIEU ’student.id’, ’19824’;
Kết quả hệ thống trả về đã lọc và trả về danh sách toàn bộ các học phần mà sinh viên này đã đăng ký:
• Thông tin hiển thị: Bao gồm StudentID, StudentName, AcademicYear, Semester, CourseTitle, thời
gian học (ClassTime), phòng học (Classroom), giảng viên (InstructorName), và khoa (DepartmentName).
• Kết quả: Sinh viên Saveileva có mã số 19824 đã tham gia rất nhiều học phần trong nhiều học kỳ
khác nhau (Fall, Spring) và các năm từ 2003 đến 2010. • Mục đích: Giúp nhanh chóng truy vấn
thông tin lịch sử học tập của một sinh viên cụ thể, hỗ trợ kiểm tra quá trình đăng ký môn và quá trình học tập.
Để kiểm thử chương trình đã tạo, ta thực hiện câu lệnh:
Hình 2: Kết quả chạy câu lệnh bài 2 lOMoARcPSD| 45315597 2 BÀI TẬP DECLARE @tbl FilterTable;
INSERT INTO @tbl (FieldName, FieldValue) VALUES
(’student.name’, ’Manber’),
(’course.title’, ’The Music of the Ramones’); EXEC SP_LOC_DU_LIEU @tbl;
Kết quả trả về là danh sách các lần đăng ký học phần của sinh viên Manber cho môn The Music of
the Ramones, bao gồm các thông tin chi tiết:
• StudentID, StudentName – Mã và tên sinh viên.
• AcademicYear, Semester – Năm học và học kỳ.
• CourseTitle – Tên môn học.
• ClassTime, Classroom – Thời gian và phòng học.
• InstructorName, DepartmentName – Giảng viên và khoa giảng dạy. 2.3 Bài tập 3
Viết thủ tục SP_LOC_DU_LIEU cho phép nhập vào một biến kiểu table gồm 2 trường: tên trường và
một giá trị của trường. Kết quả trả về là dữ liệu sau khi lọc theo danh sách các giá trị của các trường dữ liệu đó.
Bảng kết quả trả về gồm các trường: Mã sinh viên, Họ tên sinh viên, Năm học, Kỳ học, Khóa học, Thời
gian học, Phòng học, Giảng viên, Khoa viện.
Phương án giải quyết
Thủ tục SP_LOC_DU_LIEU được xây dựng để hỗ trợ lọc dữ liệu động với nhiều điều kiện cùng lúc,
dựa trên danh sách các cặp (Tên trường, Giá trị) được truyền vào dưới dạng table-valued parameter.
Giải pháp được triển khai theo các bước chính:
1. Định nghĩa kiểu bảng FilterTable
• Kiểu bảng FilterTable gồm hai cột:
– FieldName: tên trường dữ liệu cần lọc (bao gồm tên bảng), ví dụ student.name. lOMoARcPSD| 45315597 2 BÀI TẬP
– FieldValue: giá trị cần lọc.
• Cấu trúc này cho phép người dùng truyền nhiều điều kiện lọc vào cùng một lần gọi thủ tục.
2. Ánh xạ tên bảng sang bí danh (alias)
• Trong câu truy vấn chính, các bảng sử dụng alias như s, t, sec, c, ts, cl, te, i, d.
• Thủ tục thực hiện ánh xạ FieldName từ tên bảng gốc sang alias tương ứng bằng cách dùng
chuỗi các lệnh REPLACE cho từng bản ghi trong @filters.
3. Xây dựng điều kiện WHERE động
• Sử dụng hàm STRING_AGG để kết hợp các điều kiện lọc (ví dụ: s.name = ’John’ AND
c.title = ’Database’) thành một chuỗi duy nhất.
• Cách làm này giúp thủ tục xử lý được bất kỳ số lượng điều kiện lọc nào.
4. Tạo và thực thi câu truy vấn động • Câu truy vấn chính trả về danh sách thông tin sinh viên và khóa học
gồm: Mã sinh viên, Họ tên, Năm học, Kỳ học, Khóa học, Thời gian học, Phòng học, Giảng viên, Khoa viện.
• Phần điều kiện WHERE được ghép động từ chuỗi điều kiện đã xây dựng và được thực thi bằng sp_executesql. 5. Kết quả trả về
• Dữ liệu sau khi lọc được trả về chính xác theo các trường và giá trị truyền vào.
• Hỗ trợ lọc đồng thời nhiều trường khác nhau, ví dụ: lọc theo tên sinh viên và tên khóa học cùng lúc.
Listing 3: Script SQL cho bài 3
CREATE TYPE FilterTable AS TABLE (
FieldName NVARCHAR(100), -- Example: student.name, course.title FieldValue NVARCHAR(100) ); GO
DROP PROCEDURE IF EXISTS SP_LOC_DU_LIEU; GO lOMoAR cPSD| 45315597 2 BÀI TẬP
CREATE PROCEDURE SP_LOC_DU_LIEU @filters FilterTable READONLY AS BEGIN
DECLARE @sql NVARCHAR(MAX) = ’’;
DECLARE @where NVARCHAR(MAX) = ’’;
SELECT @where = STRING_AGG(mapped.ColumnName + ’ = ’’’ + f.FieldValue + ’’’’, ’ AND ’) FROM @filters f CROSS APPLY ( SELECT REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE(f.FieldName, ’student.’, ’s.’), ’takes.’, ’t.’), ’section.’, ’sec.’), ’course.’, ’c.’), ’time_slot.’, ’ts.’), ’classroom.’, ’cl.’), ’teaches.’, ’te.’), ’instructor.’, ’i.’), ’department.’, ’d.’ ) AS ColumnName ) mapped; -- Build final SQL SET @sql = ’ SELECT s.ID AS StudentID,
s.name AS StudentName, sec.year AS AcademicYear, sec.semester AS
Semester, c.title AS CourseTitle,
CONCAT(’’From ’’, ts.start_hr, ’’:’’, ts.start_min, ’’ to ’’, ts.end_hr,
’’:’’, ts.end_min) AS ClassTime, lOMoARcPSD| 45315597 2 BÀI TẬP
CONCAT(sec.building, ’’ ’’, sec.room_number) AS Classroom, i.name AS InstructorName, d.dept_name AS DepartmentName FROM student s JOIN takes t ON s.ID = t.ID
JOIN section sec ON t.course_id = sec.course_id AND t.sec_id = sec.sec_id AND t.semester = sec.semester AND t.year = sec.year
JOIN course c ON sec.course_id = c.course_id
LEFT JOIN time_slot ts ON sec.time_slot_id = ts.time_slot_id
LEFT JOIN classroom cl ON sec.building = cl.building
AND sec.room_number = cl.room_number
LEFT JOIN teaches te ON sec.course_id = te.course_id AND sec.sec_id = te.sec_id
AND sec.semester = te.semester AND sec.year = te.year
LEFT JOIN instructor i ON te.ID = i.ID
LEFT JOIN department d ON s.dept_name = d.dept_name ’
+ CASE WHEN @where IS NOT NULL AND @where <> ’’ THEN ’ WHERE ’ + @where ELSE ’’ END; EXEC sp_executesql @sql; END; GO 2.4 Bài tập 4
Sinh viên A muốn học môn ‘Mobile Computing’ hỏi A cần phải học qua những môn gì?
Phương án giải quyết
Mục tiêu của bài toán là tìm danh sách tất cả các môn học tiên quyết (prerequisite) cho một môn
học cụ thể (ví dụ: “The Music of the Ramones”). Giải pháp sử dụng CTE đệ quy (Common Table
Expression) để truy xuất cả các môn học tiên quyết trực tiếp và gián tiếp. Các bước thực hiện:
1. Định nghĩa CTE prereq_chain gồm hai phần: lOMoARcPSD| 45315597 2 BÀI TẬP
Hình 3: Kết quả chạy câu lệnh bài 3
• Truy vấn cơ sở (base query): Lấy các môn học tiên quyết trực tiếp bằng cách:
– Kết nối bảng course với prereq.
– Lọc theo môn học mục tiêu (ví dụ: title = ’The Music of the Ramones’).
• Truy vấn đệ quy (recursive query): Lấy các môn học tiên quyết của những môn đã tìm được ở
cấp trước đó (tức là tìm các điều kiện tiên quyết nhiều cấp). 2. Cơ chế đệ quy:
• Kết hợp kết quả từ cấp trước (prereq_chain) với bảng prereq.
• Liên kết thêm với bảng course để lấy tên môn học tiên quyết.
• Mỗi vòng lặp sẽ mở rộng danh sách các môn học tiên quyết lên một cấp. 3. Kết quả cuối cùng:
• Sử dụng SELECT DISTINCT để loại bỏ trùng lặp.
• Trả về mã môn học tiên quyết (MaMonTienQuyet) và tên môn tiên quyết (TenMonTienQuyet) cho môn học đã chọn.
Phương pháp này đảm bảo rằng tất cả các môn học tiên quyết, bao gồm cả tiên quyết nhiều cấp, đều
được liệt kê đầy đủ trong kết quả.
Listing 4: Script SQL cho bài 3

Tài liệu liên quan: