Quản lý thư viện trường đại học - Viết trên SQL

BỘ CÔNG NGHIỆP TRƯỜNG ĐẠI HỌC CÔNG NGHIỆP TP. HCM KHOA CÔNG NGHỆ THÔNG TIN BÁO CÁO ĐỒ ÁN MÔN HỆ CƠ SỞ DỮ LIỆU TÊN ĐỒ ÁN: QUẢN LÝ THƯ VIỆN TRƯỜNG ĐẠI HỌC TP. Hồ Chí Minh, tháng 12 năm 2009 Phần I: Thiết kế Cơ sở dữ liệu quản lý thư viện 1. Tóm tắt hệ thống: Một trường đại học vừa khánh thành 1 thư viện hiện đại với nhiều trang thiết bị và phương tiện sử dụng hiện đại. Đối tượng phục vụ của thư viện là giảng viên và sinh viên toàn trường. Thông tin đầy đủ của giảng viên và sinh viên sẽ hiển thị trên màn hình thủ thư khi máy quét thẻ đọc thẻ. Sách báo trong thư viện với đầy đủ các thể loại, đáp ứng nhu cầu phong phú của người đọc, bao gồm sách kinh tế, kỹ thuật, chính trị, ngoại văn, tài liệu luận văn, các tạp chí, báo cáo khoa học… Hình thức mượn cho sinh viên và giảng viên cũng rất phong phú: có thể mượn sách in, báo, tạp chí, đề tài tốt nghiệp sinh viên ra trường, có thể mượn đĩa CD chứa dữ liệu. Đồng thời giảng viên, sinh viên có thể tra cứu tại chỗ để tìm ebook cũng được lưu trữ và phân loại chi tiết. Nội quy thư viện cũng nghiêm ngặt.Khi người mượn xuất trình thẻ và sách cẩn mượn, thủ thư sẽ cho sách và thẻ qua máy quét (scanner) để đọc mã, truy cập vào Cơ sở dữ liệu, để hiển thị thông tin đầy đủ về người mượn và sách được mượn. Thời hạn mượn tối đa là 1 tháng. Sau 1 tháng nếu người mượn vẫn muốn mượn tiếp, thì phải đem sách đến thư viện để gia hạn. Nếu quá thời gian quy định thì sẽ bị phát tiền. Tiền phạt dựa vào cứ chậm 10 ngày thì phạt số tiền bằng với 1/10 giá quyển sách bị mượn quá hạn. Mỗi đầu sách thường có nhiều hơn 1 cuốn. Người thủ thư cần quản lý hết về số lượng sách báo hiện tại có trong thư viện, danh sách những người đang giữ sách và mã sách đã được mượn. 2. Nguyên tắc nghiệp vụ: ( business rule) Các loại sách báo, tạp chí trong thư viện dành cho giảng viên, học sinh trong trường. Mỗi giảng viên, sinh viên trong trường chỉ được mượn tối đa 2 quyển sách và sau khi trả mới cho mượn sách tiếp hoặc quyển sách đó chưa có giảng viên, sinh viên mượn. Mỗi quyển sách có thời gian mượn tối đa là 1 tháng. Nếu quá hạn phải đến thư viện xin gia hạn thêm, nếu quá thời gian quy định, người mượn sẽ bị phạt tiền. Số tiền bị phạt được tính 1/10 giá trị quyển sách nếu cứ trễ 10 ngày. Với mỗi quyển sách chỉ được viết bởi duy nhất một tác giả. Với một tác giả đó thì có thể viết một hoặc nhiều cuốn sách khác nhau. Một quyển sách chỉ thuộc duy nhất một thể loại sách trong kho sách. Trong thư viện, mỗi thể loại được cất giữ với nhiều đầu sách khác nhau cùng một chủ đề. Và một quyển sách như thế chỉ được xuất bản bởi một nhà xuất bản. Trong khi đó, một nhà xuất bản sẽ xuất bản nhiều quyển sách khác nhau thuộc nhiều thể loại khác nhau. 3. Mô hình liên kết thực thể a. Các kiểu thực thể CÁC THỰC THỂ (ENTITIES) …… b. Các thuộc tính: - Thuộc tính mỗi thực thể, kể cả thuộc tính phức hợp, đa trị,… - Thuộc tính khóa mỗi thực thể THUỘC TÍNH CỦA CÁC THỰC THỂ (ATTRIBUTES) c. Mô hình thực thể kết hợp: ….. 4. Mô hình quan hệ: - Chuyển từ mô hình ER thành mô hình quan hệ - Xác định các phụ thuộc hàm - Chuẩn hóa dữ liệu, chuyển tất cả các bảng sang dạng chuẩn 3NF CÁC BẢNG (TABLES) CHUẨN HÓA BẢNG Các bảng đã đạt dạng chuẩn 1NF và 2NF Đưa ra các phụ thuộc hàm của các bảng để chứng minh bảng đã ở chuẩn 2NF Trong bảng Orders, khóa chính là Order-Code. Thuộc tính không khóa Ship-Method ngòai việc phụ thuộc vào khóa chính thì nó còn phụ thuộc cả vào thuộc tính Ship-Method-ID . Order-Code --> Cust-Code, Emp-Code, Order-Dt, ...., Ship-Method-ID, Ship-Method Ship-Method-ID --> Ship-Method Do đó, để bảng Orders đạt 3NF chúng ta cần tạo thêm bảng mới Shipment Methods . Tương tự, trong bảng Payments , thuộc tính Payment-Mode phụ thuộc vào Payment-Mode-ID ngòai việc phụ thuộc vào khóa chính Payment-Code. Bởi vậy chúng ta cần tạo thêm bảng mới Payment Modes. Ngòai ra, thuộc tính Cardholders-Name và CreditCardExpiry-Dt phụ thuộc vào CreditCard-No ngòai việc phụ thuộc vào khóa chính Payment-Code . Bởi vậy, chúng ta cần tạo thêm table Credit Cards . Mô hình sau khi chuẩn hóa sẽ gồm các bảng đạt 3NF như sau : KHÓA CHÍNH VÀ CÁC KHÓA NGỌAI: MỐI LIÊN HỆ GIỮA CÁC TABLE Phần 2: Thực thi cơ sở dữ liệu 1. Tạo cơ sở dữ liệu và các bảng: - Cơ sở dữ liệu FriendHood được tạo trong SQL server với 2 tập tin cơ bản là: FriendHood.mdf và FriendHood.ldf ( xem script CreateFriendHoodDatabase.sql) - Dựa vào yêu cầu hệ thống, các bảng của CSDL này đã được xây dựng như sau: Bảng Customers Trường Cust_Id là khóa chính và được tạo tự động Các trường FirstName, LastName, Address, City, State, và Phone không nên để trống Trường Phone nên có dạng sau 0000-000-000. Bảng Employees Trường Emp_Id là khóa chính và được tạo tự động Trường Phone nên theo dạng 0000-000-000. Trường Title (chức vụ) nên là 1 trong các chức vụ sau: ‘Executive’, ’Senior Executive’, ‘Manager’, ’Chairman’. Các trường FirstName và LastName không nên đề trống Bảng Products Trường Product_Id là khóa chính và nên có định dạng sau: ‘P[0-9][0-9][0-9]’ Trường Unit Price phải lớn hơn 0 Các trường còn lại không nên để trống Bảng PaymentMethods Trường PaymentMethod_Id là khóa chính và nên có dạng sau ‘PM[0-9][0-9][0-9]’ Trường description không nên để trống Bảng ShipmentMethods Trường ShipmentMethod_Id là khóa chính và nên có dạng sau :‘SH[0-9][0-9][0-9]’ Trường description không nên để trống Bảng Orders Trường Order_Id là khóa chính và nên có dạng sau ‘O[0-9][0-9][0-9]’ Các trường shipment name, shipment address, shipment city, shipment state, shipment phone, shipment country, shipment date, freight charge, và tax không nên để trống Trường phone nên có dạng ‘[0-9][0-9][0-9][0-9]-[0-9][0-9][0-9]-[0-9][0-9][0-9]’ Trường Order date nên có giá trị mặc định lả ngày hiện hành Các trường Freight charge (cước phí), Tax (thuế) phải lớn hơn 0 Trường Shipment Date không thể sớm hơn trưởng order date. Bảng OrderDetails Trường OrderDetail_Id là khóa chính và được tạo tự động Trường quantity (số lượng) và discount (chiết suất) không được để trống Trường Total cost được tính tự động Trường Quantity phải lớn hơn 0 Bảng Orderdetails có khóa ngoại là Order_Id vàa tuân theo quy luật bảo tòan tham chiếu Bảng Payments Trường Payment_Id là khóa chính và được tạo tự động Trường payment amount phải lớn hơn 0 và bằng tổng của trường total costs của cùng 1 đơn đặt hàng trong bảng OrderDetails table. Nếu không bằng nhau, thi giao dịch này sẽ bị từ chối và sẽ hiển thị báo lỗi cho người dùng Trường shipment date phải chậm hơn payment date ( ngày thanh tóan), mặc định là 2 ngày Trường payment date phải sau order date (ngày đặt hàng). Trường Credit card number (số thẻ tín dụng) nên có định dạng sau ‘[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]-[0-9][0-9][0-9]’. Các trường payment amount và payment date không nên để trống Nếu trường payment method là ‘Credit Card’ thì phải bảo đảm được là trường credit card details không được để trống Ngày Expiry date (ngày hết hạn) của thẻ tín dụng phải sau ngày hiện hành Bổ sung các constraint, rule và default (nếu có). Lý do tạo ra.. 2. Quy tắc nghiệp vụ và các trigger: Để thể hiện quy tắc nghiệp vụ thứ ….. của công ty, ta cần xây dựng trigger như sau: …….. Script để xây dựng các bảng được ghi lại trong các tập tin sau: CreateFriendHoodObjects.sql: chứa các lệnh tạo bảng. Nó cũng chứa lệnh insert để thêm 1 vài bản ghi mẫu vào các bảng CreateFriendHoodConstraints.sql: chứa các constraint, rule và defauft của 1 số bảng. CreateFriendHoodProcedures.sql: chứa một vài thủ tục và trigger của CSDL CreateFriendHoodIndexes.sql: chứa các lệnh tạo chỉ mục để tăng tốc thực thi cho 1 số lệnh truy vấn hay dùng ErrorDetectionForProceduresandTriggers.sql: chứa các lệnh dùng để kiểm tra tính hợp lệ của các đối tượng trong CSDL