ĐẠI HỌC QUỐC GIA THÀNH PHỐ HỒ CHÍ MINH TRƯỜNG ĐẠI HỌC BÁCH KHOA KHOA KHOA HỌC - KỸ THUẬT MÁY TÍNH HỆ CƠ SỞ DỮ LIỆU Thiết kế cơ sở dữ liệu & hiện thực Mô hình nhà sách trực tuyến (E-Book store) GVHD: Đỗ Thanh Thái SV: Nguyễn Hữu Khang - 2011365 Nguyễn Hoàng Trí Viễn - 2015043 Đậu Xuân Thành - 2014486 TP. HỒ CHÍ MINH, THÁNG 10/2022 Mục lục 1 Insert dữ liệu mẫu vào cơ sở dữ liệu 2 2 Viết các Functions/Procedures/Assertions/Triggers 5 3 Định nghĩa các nhóm người dùng và phân quyền ở mức ứng dụng 37 4 Định nghĩa kiến trúc cho ứng dụng 38 4.1 Mô hình MVC .2 Kiến trúc ứng dụng thực tế của nhóm .3 Minh họa kiến trúc ứng dụng. 38 5 Thiết kế chi tiết cho ứng dụng 39 6 Chuẩn hóa lược đồ về dạng chuẩn BCNF cho lược đồ quan hệ: 47 7 So sánh dữ liệu trong cơ sở dữ liệu và dữ liệu trong các tập tin như text files khi phát triển ứng dụng cho đề tài của assignment 60 8 Cài đặt chỉ mục, minh họa đo đạc: so sánh thời gian thực thi chỉ mục với demo lớn: 76 Trường Đại Học Bách Khoa Tp.Hồ Chí Minh Khoa Khoa Học và Kỹ Thuật Máy Tính 1 Insert dữ liệu mẫu vào cơ sở dữ liệu • Bảng Account: Insert một số dữ liệu mẫu vào bảng Account như sau: Kết quả dữ liệu đã được insert thành công: Bài tập lớn môn Hệ cơ sở dữ liệu - Niên khóa 2022-2023 Trang 2/80 Trường Đại Học Bách Khoa Tp.Hồ Chí Minh Khoa Khoa Học và Kỹ Thuật Máy Tính – Bảng Product: Insert một số dữ liệu mẫu vào bảng Product như sau: Kết quả dữ liệu đã được insert thành công: * Ở đây nhóm có bổ sung thêm thuộc tính Typeproduct để phân biệt loại sản phẩm giữa sách với tạp chí (với Typeproduct của Sách là 1 và Typeproduct của tạp chí là 2). – Bảng Orders: Insert một số dữ liệu mẫu vào bảng Orders như sau: Bài tập lớn môn Hệ cơ sở dữ liệu - Niên khóa 2022-2023 Trang 3/80 Trường Đại Học Bách Khoa Tp.Hồ Chí Minh Khoa Khoa Học và Kỹ Thuật Máy Tính Kết quả dữ liệu đã được insert thành công: Bài tập lớn môn Hệ cơ sở dữ liệu - Niên khóa 2022-2023 Trang 4/80 Trường Đại Học Bách Khoa Tp.Hồ Chí Minh Khoa Khoa Học và Kỹ Thuật Máy Tính 2 Viết các Functions/Procedures/Assertions/Triggers • Bài làm của Nguyễn Hữu Khang – Trigger: Trigger trên bảng book: Trong một số trường hợp nhất định, nếu sách trong cửa hàng bị thất lạc, hoặc bị mất thì chủ cửa hàng phải cập nhật lại số lượng còn lại trong cửa hàng của cuốn sách đã bị thất lạc đó.
Sau khi cập nhật lại số lượng sách trong cửa hàng, nếu số sách hiện có là ít hơn số sách có trong một đơn đặt hàng của một khách hàng và đơn hàng đó còn ỏ trạng thái đang xử lý (Processing) thì đơn hàng sẽ chuyển sang trạng thái đã hủy (Canceled) CREATE DEFINER=‘root‘@‘localhost‘ TRIGGER ‘book_AFTER_UPDATE‘ AFTER UPDATE ON ‘book‘ FOR EACH ROW BEGIN DECLARE done INT DEFAULT FALSE; DECLARE x int; DECLARE mycur CURSOR FOR SELECT ORDERID FROM order_detail, orders WHERE Quantity > New.Quantity_in_store and ORDERID=Order_ID and PID = New.Product_ID and Status like ’%Processing%’ ; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; open mycur; My_Loop : loop fetch mycur into x; if done then Leave My_Loop; end if; UPDATE orders SET Status = ’Canceled’ WHERE (Order_ID = x); END loop; CLOSE mycur; END Câu lệnh để kiểm tra thủ tục hoạt động: VD: Đơn hàng có ID là 102, đang có trạng thái đơn hàng là: Processing (đang xử lý). Ta xem chi tiết đơn hàng có ID là 102 ở bảng order_detail thì thấy rằng đơn hàng này bao gồm 10 sản phẩm ID là 1. Bài tập lớn môn Hệ cơ sở dữ liệu - Niên khóa 2022-2023 Trang 5/80 Trường Đại Học Bách Khoa Tp.Hồ Chí Minh Khoa Khoa Học và Kỹ Thuật Máy Tính Lúc này chuyển sang bảng Book ta thấy ở cuốn sách có ID = 1 thì số lượng sách trong cửa hàng hiện tại là 50 cuốn. Lúc này ta thay đổi số lượng sách thành một số bất kỳ bé hơn 10 và lưu lại.
Lúc này ta quay lại bảng đơn hàng (orders) để kiểm tra thì thấy rằng đơn hàng có ID là 102 đã được chuyển sang trạng thái Canceled từ trạng thái ban đầu là Processing. Bài tập lớn môn Hệ cơ sở dữ liệu - Niên khóa 2022-2023 Trang 6/80 Trường Đại Học Bách Khoa Tp.Hồ Chí Minh Khoa Khoa Học và Kỹ Thuật Máy Tính – Procedures: Thủ tục 1: Tìm top những cuốn sách bán chạy nhất. Thủ tục này có tên là topsachbanchay. Chức năng của thủ tục này là để hiển thị các thông tin của top 5 các cuốn sách bán chạy nhất như: Thumbnail, Tên sách, Gía Tiền, Thể loại sách và tổng số sách đã bán.
Lưu ý: tổng số lượng đã bán của từng cuốn sách chỉ được cộng lại từ những đơn hàng đã ở trạng thái hoàn thành (Completed). CREATE DEFINER=‘root‘@‘localhost‘ PROCEDURE ‘topsachbanchay‘() BEGIN select Thumbnail, P.Name as TenSach, P.Discount_price as GiaTien, C.Name as Theloaisach ,sum(O.Quantity) as Tongsosachdaban FROM product P, book B, category C, order_detail O, orders D where P.Product_ID and B.Category_ID and P.Order_ID and D.Status like ’%Completed%’ group by Thumbnail, TenSach, GiaTien, Theloaisach ORDER BY sum(O.Quantity) desc limit 5; END Câu lệnh để kiểm tra thủ tục hoạt động: Chúng ta gọi thủ tục topsachbanchay như sau: call baitaplon.topsachbanchay(); Kết quả hiện thị ra màn hình thể hiện có 2 sản phẩm đang bán chạy là "Truyện thám tử lừng danh conan tập 98" và "Đắc nhân tâm". Tuy thủ tục này lấy ra top 5 sản phẩm bán chạy nhưng kết quả hiện ra chỉ có 2 sản phẩm vì trong bảng chi tiết đơn hàng (order_detail) chỉ mới có 2 sản phẩm (PID = 1 và PID = 2) được mua. Bài tập lớn môn Hệ cơ sở dữ liệu - Niên khóa 2022-2023 Trang 7/80 Trường Đại Học Bách Khoa Tp.Hồ Chí Minh Khoa Khoa Học và Kỹ Thuật Máy Tính • Bài làm của Nguyễn Hoàng Trí Viễn – Trigger: Trigger trên bảng Product: Trigger sẽ tự động thêm vào bảng Book hoặc bảng Magazine_seri khi ta nhập một sản phẩm mới vào bảng Product.
Ví dụ nếu ta thêm mới một sản phẩm ở bảng Product và loại sản phẩm này là sách (Typeproduct = 1) thì trigger này sẽ fire và sẽ thêm vào bảng Book với ProductID (khóa chính của bảng Book) cũng chính là ProductID khóa chính của bảng Product được thêm, tương tự với sản phẩm là tạp chí. CREATE DEFINER=‘root‘@‘localhost‘ TRIGGER ‘product_AFTER_INSERT‘ AFTER INSERT ON ‘product‘ FOR EACH ROW BEGIN if(NEW.Typeproduct = 1) then INSERT INTO book(Product_ID) values (NEW.Product_ID); elseif(NEW.Typeproduct = 2) then INSERT INTO magazine_seri(Product_ID) values (NEW.Product_ID); end if; END CREATE DEFINER=‘root‘@‘localhost‘ TRIGGER ‘product_AFTER_UPDATE‘ AFTER UPDATE ON ‘product‘ FOR EACH ROW BEGIN if(NEW.Typeproduct = 1) then INSERT INTO book(Product_ID) values (NEW.Product_ID); elseif(NEW.Typeproduct = 2) then INSERT INTO magazine_seri(Product_ID) values (NEW.Product_ID); end if; END Câu lệnh để kiểm tra trigger hoạt động: Insert du lieu cua mot cuon sach (Typeprodct = 1) vao trong bang Product INSERT INTO ‘baitaplon‘.‘product‘ (‘Thumbnail‘, ‘Name‘, ‘Price‘, ‘Discount_price‘, ‘Publisher‘, ‘Description‘, ‘Typeproduct‘) VALUES (’https://m.com/images/I/41SNoh5ZhOL.jpg’, ’Introduction to Algorithms’, ’200000’, ’180000’, ’Nha xuat ban giao duc’, ’Cuon sach giup ban hieu ro ve cac giai thuat’, ’1’); Hình ảnh sau khi Insert thành công : Bài tập lớn môn Hệ cơ sở dữ liệu - Niên khóa 2022-2023 Trang 8/80 Trường Đại Học Bách Khoa Tp.Hồ Chí Minh Khoa Khoa Học và Kỹ Thuật Máy Tính Kiểm tra thấy rằng trong bảng Book thì sách có Product_ID = 5 đã được thêm vào thành công -> Trigger đã hoạt động. Bài tập lớn môn Hệ cơ sở dữ liệu - Niên khóa 2022-2023 Trang 9/80 Trường Đại Học Bách Khoa Tp.Hồ Chí Minh Khoa Khoa Học và Kỹ Thuật Máy Tính Trigger trên bảng Account: Vì một số lý do nên cửa hàng yêu cầu đỗ tuổi nhất định cho mỗi loại tài khoản người dùng. Với tài khoản là nhân viên của cửa hàng (nhân viên thường, nhân viên kho) và quản lý của hàng thì độ tuổi yêu cầu là từ 18 tuổi trở lên.
Với tài khoản là khách hàng thì độ tuổi yêu cầu là từ 12 tuổi. Sau đây là trigger sau khi insert trên bảng account, nếu tuổi của tài khoàn bé hơn độ tuổi quy định thì sẽ xuất hiện thông báo lỗi. Ta sẽ phân biệt giữa khách hàng, nhân viên thường, nhân viên quản lý kho và quản lý thông qua khóa ngoại ROLE_NO tham chiếu đến khóa chính Role_ID của bảng Role. CREATE DEFINER=‘root‘@‘localhost‘ TRIGGER ‘account_BEFORE_INSERT‘ BEFORE INSERT ON ‘account‘ FOR EACH ROW BEGIN IF(NEW.ROLE_NO = 4 and (datediff(curdate(),NEW.Birthday)/365 < 12)) THEN SIGNAL SQLSTATE ’45000’ SET MESSAGE_TEXT = ’Age is not allowed to be less than 12’; ELSEIF(datediff(curdate(),NEW.Birthday)/365 < 18) THEN SIGNAL SQLSTATE ’45000’ SET MESSAGE_TEXT = ’Age is not allowed to be less than 18’; END IF; end Câu lệnh để kiểm tra trigger hoạt động: Ta thử nhập thông tin của khách hàng (với ROLE_NO = 4) và một ngày sinh không thỏa yêu cầu, VD: 2015-10-10 INSERT INTO ‘baitaplon‘.‘account‘ (‘FName‘, ‘LName‘, ‘TelephoneNum‘, ‘Address‘, ‘Birthday‘, ‘Email‘, ‘Password‘, ‘ROLE_NO‘) VALUES (’An’, ’Nguyen’, ’0123412516’, ’Quang Ngai’, ’2015-10-10’, ’anan@gmail.com’, ’abc123’, ’4’); Hình ảnh thông báo lỗi vì ngày sinh của người dùng so với ngày hiện tại của hệ thống là bé hơn 12 năm : Bài tập lớn môn Hệ cơ sở dữ liệu - Niên khóa 2022-2023 Trang 10/80 Trường Đại Học Bách Khoa Tp.Hồ Chí Minh Khoa Khoa Học và Kỹ Thuật Máy Tính Ta thử nhập thông tin của nhân viên quản lý kho (ROLE_NO = 3) và một ngày sinh không thỏa yêu cầu, VD: 2008-10-10 INSERT INTO ‘baitaplon‘.‘account‘ (‘FName‘, ‘LName‘, ‘TelephoneNum‘, ‘Address‘, ‘Birthday‘, ‘Email‘, ‘Password‘, ‘ROLE_NO‘) VALUES (’An’, ’Nguyen’, ’0123412516’, ’Quang Ngai’, ’2008-10-10’, ’anan@gmail.com’, ’abc123’, ’3’); Hình ảnh thông báo lỗi vì ngày sinh của người dùng so với ngày hiện tại của hệ thống là bé hơn 18 năm : Bài tập lớn môn Hệ cơ sở dữ liệu - Niên khóa 2022-2023 Trang 11/80 Trường Đại Học Bách Khoa Tp.Hồ Chí Minh Khoa Khoa Học và Kỹ Thuật Máy Tính – Procedures: Thủ tục 1: Về nhập dữ liệu trên bảng Account Thủ tục insert trong bảng Account có tên là insertaccount.
Chức năng của thủ tục này để kiểm tra xem giá trị của các thuộc tính nhập vào như: TelephoneNum, email, password ,. có đúng định dạng hay chưa. Các định dạng để kiểm tra input đầu vào như sau: +) TelephoneNum chỉ bao gồm các chữ số và bắt đầu bằng chữ số 0. +) Email phải theo định dạng something@something.
+) Password phải có độ dài lớn hơn hoặc bằng 8 kí tự, chỉ bao gồm chữ viết thường, chữ viết hoa và chữ số. +) Họ, tên của chủ tài khoản chỉ bao gồm chữ viết thường và viết hoa. CREATE DEFINER=‘root‘@‘localhost‘ PROCEDURE ‘insertaccount‘(FName varchar(255),LName varchar(255),TelephoneNum varchar(15),Address varchar(255),Birthday date,email varchar(255),passwordd varchar(255),Roleno int) BEGIN IF((TelephoneNum REGEXP ’^0[0-9]+$’)is false) THEN SIGNAL SQLSTATE ’45000’ SET MESSAGE_TEXT = ’Format error (Telephone_number)’; ELSEIF(length(passwordd)<8 or ((passwordd REGEXP ’^[a-zA-Z0-9]+$’)is false)) THEN SIGNAL SQLSTATE ’45000’ SET MESSAGE_TEXT = ’Format error (Password)’; ELSEIF ((email REGEXP ’^[^@]+@[^@]+\.