Mô tả

Bạn được giao xây dựng một hệ thống quản lý thư viện với các thực thể: Thể loại sách (Category), Tác giả (Author), Sách (Book), Độc giả (Reader), Mượn trả (BorrowReturn). Thực hiện các yêu cầu dưới đây.

Phần 1: Tạo Cơ sở dữ liệu và các bảng

1. Tạo database LibraryManagement.

2. Tạo các bảng:

   • Category

     • CategoryID: INT NOT NULL

     • CategoryName: NVARCHAR(100)

   • Author

     • AuthorID: INT NOT NULL

     • AuthorName: NVARCHAR(100)

     • Country: NVARCHAR(100)

   • Book

     • BookID: INT NOT NULL

     • Title: NVARCHAR(200)

     • PublishYear: INT

     • Price: DECIMAL(10,2)

     • CategoryID: INT

     • AuthorID: INT

   • Reader

     • ReaderID: INT NOT NULL

     • ReaderName: NVARCHAR(100)

     • BirthDate: DATE

     • PhoneNumber: NVARCHAR(15)

   • BorrowReturn

     • BorrowID: INT NOT NULL

     • BookID: INT NOT NULL

     • ReaderID: INT NOT NULL

     • BorrowDate: DATETIME

     • ReturnDate: DATETIME NULL

3. Thiết lập:

   • Khóa chính (PRIMARY KEY) cho mỗi bảng.

   • Khóa ngoại:

     • Book.CategoryID → Category.CategoryID

     • Book.AuthorID → Author.AuthorID

     • BorrowReturn.BookID → Book.BookID

     • BorrowReturn.ReaderID → Reader.ReaderID

   • Ràng buộc CHECK:

     • PublishYear phải lớn hơn 1900 và nhỏ hơn hoặc bằng năm hiện tại (GETDATE()).

     • Price >= 0.

     • Reader.BirthDate không được lớn hơn ngày hiện tại.

   • Ràng buộc UNIQUE nếu cần:

     • Số điện thoại trong Reader không được trùng lặp.

4. Thực hiện ALTER TABLE để thêm các ràng buộc (nếu bạn ban đầu tạo bảng mà chưa có).

Phần 2: Dữ liệu mẫu

Chèn vào:

• Ít nhất 4 thể loại sách.

• Ít nhất 5 tác giả từ các quốc gia khác nhau.

• Ít nhất 8 sách, thuộc các thể loại khác nhau, năm xuất bản & giá khác nhau, mỗi sách có một tác giả.

• Ít nhất 5 độc giả với ngày sinh hợp lý & số điện thoại.

• Ít nhất 10 bản ghi mượn trả (BorrowReturn), có ReturnDate cho một số, cho một số vẫn NULL (chưa trả).

Phần 3: Thao tác & truy vấn

Viết các câu SQL thực hiện:

1. Tạo cột mới trong bảng Book: Stock (INT) — số lượng sách hiện có trong kho.

2. Cập nhật một vài cuốn sách để có Stock khác nhau (ví dụ, 0, 5, 10…).

3. Truy vấn tất cả các sách (BookID, Title, Price, CategoryName, AuthorName).

4. Truy vấn các cuốn sách có giá > 200 và năm xuất bản từ 2000 đến hiện tại.

5. Truy vấn các đọc giả cùng với số lượng sách họ đang mượn (ReturnDate IS NULL).

6. Tạo VIEW view_Books_By_Author gồm AuthorName, BookID, Title, sắp xếp theo AuthorName.

7. Tạo VIEW view_Overdue để hiển thị các bản ghi mượn trả mà ngày mượn cách ngày hiện tại đã hơn 30 ngày và chưa trả (ReturnDate IS NULL), gồm BorrowID, ReaderName, BookTitle, BorrowDate.

8. Tạo Stored Procedure sp_UpdateBookPrice:

   • Tham số vào: @BookID INT, @NewPrice DECIMAL(10,2).

   • Kiểm tra nếu @NewPrice < 0 thì không thực hiện update, trả về lỗi hoặc thông báo.

   • Nếu hợp lệ, cập nhật giá mới cho sách có BookID.

9. Tạo Stored Procedure sp_GetReaderBorrowHistory:

   • Tham số vào: @ReaderID INT.

   • Trả về danh sách các sách đã mượn bởi Reader này, gồm BookID, Title, BorrowDate, ReturnDate.

10. Tạo Trigger trg_BorrowReturn_Insert trên bảng BorrowReturn:

    • Khi insert, nếu BorrowDate > ngày hiện tại thì rollback transaction và báo lỗi: “Ngày mượn không thể trong tương lai”.

11. Tạo Trigger trg_Book_Delete trên bảng Book:

    • Khi xóa sách, nếu có bản ghi mượn trả mà ReturnDate là NULL (chưa trả) đối với cuốn sách đó thì không cho phép xóa và xuất thông báo “Không thể xoá cuốn sách đang được mượn”.

Phần 4: Các câu hỏi lý thuyết

A. Giải thích sự khác biệt giữa CREATE TABLE và ALTER TABLE. Khi nào dùng ALTER?

B. Khi nào nên sử dụng ràng buộc CHECK? Và ưu nhược điểm của CHECK so với kiểm tra trong ứng dụng (application)?

C. View có những lợi ích gì? Lợi ích và hạn chế của việc dùng view?

D. Stored Procedure vs Inline SQL Queries: những ưu điểm khi dùng SP? Khi nào SP có thể không phù hợp?

E. Trigger là gì? Kể các trường hợp nên sử dụng trigger và các trường hợp nên tránh.