ĐỀ THI HẾT MÔN HỌC CÁC HỆ CƠ SỞ DỮ LIỆU
1) Transaction là gì, mục đích và cơ chế triển khai của nó trong SQL Server. Cho 1 ví dụ về 1
transaction và viết Script SQL thực thi transaction đó.
•Transaction (giao tác) là một dãy các thao tác cần thực hiện trên cơ sở dữ liệu dưới một đơn
vị duy nhất, nghĩa là hoặc thực hiện tất cả các thao tác hoặc không thực hiện thao tác nào
cả.
•Transaction (Giao dịch) trong SQL Server là một tập hợp tuần tự của các câu lệnh SQL đơn
để tạo thành một đơn vị luận lý duy nhất. Đơn vị luận lý này làm việc phải đảm bảo 4 tính
chất: tính nguyên tử (atomicity), tính nhất quán (consistency), tính cô lập (isolation) và tính
bền vững (durability) - (ACID).
•Ví dụ: giao tác chuyển khoản A sang B gồm 2 thao tác sau:
– Trừ tiền A.
– Cộng tiền B.
Chuyển khoản được thực hiện dưới dạng giao tác (transaction) nghĩa là hoặc thực hiện cả 2 việc trừ
tiền A và cộng tiền B hoặc nếu có sự cố thì không làm gì cả và thông báo giao tác thất bại.
•Mục đích: Giải quyết các vấn đề:
oVấn đề mất dữ liệu đã cập nhật.
oVấn đề không thể đọc lại.
oVấn đề dữ liệu không nhất quán.
•Cài transaction trên SQL Server: begin transaction, commit, rollback, save transaction …
•Ví dụ về mất dữ liệu đã cập nhật:
oVí dụ 1: Nhà sách còn 500 quyển sách.
– Vào lúc T1 nhân viên A nhận yêu cầu mua 400 quyển từ khách hàng X.
– Cũng vào lúc T1 nhân viên B nhận yêu cầu mua 300 quyển từ khách hàng Y.
– A và B đọc dữ liệu thấy còn 500 quyển nên đều đồng ý bán.
oVí dụ 2: Nhà sách còn 500 quyển sách.
– Vào lúc T2 nhân viên A sẽ thực hiện cập nhật số sách từ 500 thành 100.
– Vào lúc T3 nhân viên B sẽ thực hiện cập nhật số sách từ 500 thành 200.
− Như vậy thao tác cập nhật của A không có tác dụng hay dữ liệu mà A cập nhật sẽ bị mất vì
B cập nhật sau. (last in wind)
•Ví dụ về dữ liệu không thể đọc lại:
oVí dụ 1: Giả sử nhà sách còn 200 quyển sách.
– Vào lúc T1 nhân viên A bán cho khách 150 quyển, sẽ thực hiện cập nhật số sách từ 200
thành 50. (Giao dịch chưa hoàn thành chẳng hạn vì việc giao nhận tiền chưa xong).
– Sau đó lúc T2, B nhận được yêu cầu mua 100 quyển sách, nếu B được đọc dữ liệu chưa
hoàn tất thì B sẽ từ chối bán 100 quyển sách này.
oVí dụ 2: Giả sử nhà sách còn 200 quyển sách.
– Nếu vào lúc T3 vì lý do nào đó chẳng hạn không đủ tiền khách hàng của A không mua 150
quyển sách nữa. Giao tác bán hàng của A sẽ không thể thực hiện nên quay vê trạng thái số sách
còn là 200.
– Nhưng B đã từ chối khách hàng.
– Nếu B không đọc được dữ liệu từ lúc T1 đến T3 thì sẽ như thê nào?
•Ví dụ về dữ liệu không nhất quán: Giả sử nhân viên C cân tổng hợp 5 dòng dữ liệu 1 2 3 4 5
để làm một bản báo cáo.
– T1 : C đọc và đưa các dòng 1 2 3 4 vào báo cáo.
– T2 : D lại xóa dòng 1 thay bằng dòng 6.
– T3 : C đọc tiếp 5 6 đưa vào báo cáo.
– Vậy báo cáo này xử lý cả dữ liệu cũ và mới
→
sai.
•Transaction in SQL Server
oTransaction Recovery: 2 thao tác
– COMMIT: hoàn tât giao tác thành công.
– ROLLBACK: giao tác thất bại quay vê trạng thái trước khi thực hiện giao tác
oTransaction Log: undo the changes
– Khi user muốn một chỉnh sửa dữ liệu, trong transaction log lưu 2 phiên bản của dòng dữ
liệu đó: trước và sau khi chỉnh sửa.
− Nếu user thực hiện câu lệnh Commit thì end-of-transaction được ghi xuống transaction log.
− Nếu user thực hiện câu lenh Rollback thì hệ thống sẽ tìm phiên bản trước khi chỉnh sửa và
cập nhật lại vào CSDL.
•Ví dụ về đảm bảo tính nhất quán của dữ liệu:
Một nhân viên có mã ‘000002’ được chọn làm ‘Sales Manager’ (position code = ‘0001’). Ta
sẽ cần cập nhật trong bảng Employee và cả trong bảng Position số lượng người hiện tại
nắm giữ vị trí có Position Code = ‘0001’.
UPDATE Employee
SET cCurrentPosition = '0001'
WHERE cEmployeeCode = '000002‘
UPDATE Position
SET iCurrentStrength = iCurrentStrength + 1
WHERE cPositionCode = '0001’
Giải quyết:
BEGIN TRANSACTION trnUpdatePosition
UPDATE Employee
SET cCurrentPosition = '0001'
WHERE cEmployeeCode = '000002'
UPDATE Position
SET iCurrentStrength = iCurrentStrength + 1
WHERE cPositionCode = '0001'
COMMIT TRANSACTION trnUpdatePosition
2) Cho bảng SinhVien (MASV, TenNV, Tuoi, MaKhoa) với MASV là khóa chính. Trong cơ sở dữ
liệu hiện có 2 sinh viên: sinh viên Nguyễn Văn A có mã số SV001, 18 tuổi và thuộc khoa Toán
và sinh viên Nguyễn Văn B có mã số SV002, 22 tuổi và thuộc khoa Lý. Cho bảng Khoa
(MaKhoa, TenKhoa) với MaKhoa là khóa chính. Trong CSDL hiện có 2 khoa: khoa Toán có mã
khoa là MT và khoa Lý có mã khoa là ML.
a) Viết câu lệnh tạo bảng với khóa chính khóa ngoại thích hợp và các câu lệnh insert dữ liệu
vào các bảng trên.
Create table Khoa(
MaKhoa nchar(10) primary key,
TenKhoa nchar(30)
)
Create table SinhVien(
MASV char(10) primary key,
TenNV nchar(50),
Tuoi int,
MaKhoa nchar(10) references Khoa(MaKhoa)
)
Insert into Khoa (MaKhoa, TenKhoa)
values (‘MT’, N’Khoa Toán’)
Insert into Khoa (MaKhoa, TenKhoa)
values (‘ML’, N’Khoa Lý’)
Insert into SinhVien (MASV, TenNV, Tuoi, MaKhoa)
values (‘SV001’, N’Nguyễn Văn A’, 18, ‘MT’)
Insert into SinhVien (MASV, TenNV, Tuoi, MaKhoa)
Values (‘SV002’, N’Nguyễn Văn B’, 22, ‘ML’)
b) Viết câu lệnh tạo bảng ảo vw_SinhVienKhoa có các thông tin MASV, TenSV và TenKhoa
của sinh viên.
CREATE VIEW vw_SinhVienKhoa
AS
SELECT MASV, TenSV, TenKhoa
FROM SinhVien
JOIN Khoa
ON SinhVien.MaKhoa = Khoa.MaKhoa
c) Viết câu lệnh tạo store để thêm một khoa với 2 tham số. Sau đó dùng store này thêm 1
Khoa vào CSDL. Viết câu lệnh tạo trigger không cho phép thêm sinh viên có tuổi nhỏ hơn
18.
CREATE PROCEDURE prc_ThemKhoa(
@MaKhoa nchar(10),
@TenKhoa nchar(30))
AS
INSERT INTO Khoa (@MaKhoa, @TenKhoa)
EXECUTE prc_ThemKhoa ‘MH’, N’Khoa Hóa’
CREATE TRIGGER trgThemSinhVien
ON SinhVien
FOR INSERT
AS
DECLARE @Tuoi int
SELECT @Tuoi = Tuoi
FROM SinhVien
IF @Tuoi < 18
BEGIN
PRINT ‘Tuoi sinh vien phai lon hon 18 !’
ROLLBACK TRANSACTION
END
3) Vẽ cây phân tích <SFW> của câu truy vấn sau và biến đổi sang đại số quan hệ:
Select TenSV, Tuoi
From SinhVien, Khoa
Where MaKhoa in (select MaKhoa from Khoa where TenKhoa like ‘L%’) and Tuoi > 20
<Query>
<SFW>
SELECT <Select list> FROM <From list> WHERE <Condition>
Attribute> , <Attribute> <Relation> , <Relation>
TenSV Tuoi SinhVien Khoa
<Condition> AND <Condition>
<Attribute> > <Attribute> <Attribute> IN <Attribute>
Tuoi 20 MaKhoa <Query>
<SFW>