Database Analysis and Design môn Cơ sở dữ liệu | Đại học Văn Lang

! TEAM: 04 Version: 0.2 Date: 08 Jan, 2024 ! ! DATABASE ANALYSIS AND DESIGN
QUẢN LÍ CỬA HÀNG MỸ PHẨM
CLASS: 223_71ITIS30103_01 Team04 !
Lecturer: Ly Thi Huyen Chau Page: 1 ! TEAM: 04 Version: 0.2 Date: 08 Jan, 2024 ! HISTORY
* Nhóm trưởng soạn thảo lại nội dung này để GV dễ chấm điểm cho từng thành viên. No Content Versi Members on
Lê Chí Huy, Võ Việt Hà, Bùi Nhật Hào, 1 Project Description
Nguyễn Thiên Sơn, Nguyễn Thái Hoàng, Nguyễn Hoàng Tấn 0.1
Lê Chí Huy, Võ Việt Hà, Bùi Nhật Hào, 2 ERD Nguyễn Thiên Sơn
Võ Việt Hà, Lê Chí Huy, Nguyễn Thái 3 Business Constraint
Hoàng, Nguyễn Hoàng Tấn
Lê Chí Huy, Võ Việt Hà, Bùi Nhật Hào, 4 Database Schema
Nguyễn Thiên Sơn, Nguyễn Thái Hoàng, Nguyễn Hoàng Tấn
Lê Chí Huy, Võ Việt Hà, Bùi Nhật Hào, 5 Logic Data Diagram Nguyễn Thiên Sơn 0.2
Võ Việt Hà, Lê Chí Huy, Nguyễn Thái 6 Physical Data Diagram
Hoàng, Nguyễn Hoàng Tấn 7 SQL Programming
Võ Việt Hà, Lê Chí Huy
Effort: (phần này nộp đồ án Giai đoạn 2 mới chấm) No Members Version Effort 1 Võ Việt Hà 100% 2 Nguyễn Thái Hoàng 90% 3 Lê Chí Huy 100% 0.2 4 Nguyễn Hoàng Tấn 80% 5 Bùi Nhật Hào 100% 6 Nguyễn Thiên Sơn 100% !
Lecturer: Ly Thi Huyen Chau Page: 2 ! TEAM: 04 Version: 0.2 Date: 08 Jan, 2024 ! CONTENTS
* Sinh viên nhớ cập nhật lại mục lục này sau khi đã cập nhật nội dung trong file. ! CLASS: 202_DIT0070_05! 1!
CHAPTER 1: PROJECT DESCRIPTION! 4! CHAPTER 2: ERD! 5!
CHAPTER 3: BUSINESS CONSTRAINT! 5!
CHAPTER 4: DATABASE SCHEMA! 6!
CHAPTER 5: LOGIC DATA DIAGRAM! 7!
CHAPTER 6: PHYSICAL DATA DIAGRAM! 11! ! !
Lecturer: Ly Thi Huyen Chau Page: 3 ! TEAM: 04 Version: 0.2 Date: 08 Jan, 2024 !
CHAPTER 1: PROJECT DESCRIPTION
* Sinh viên viết Mô tả đồ án dựa vào Mô tả mà người dùng cung cấp (Đề bài), sau
đó thay đổi/bổ sung nội dung cho hoàn chỉnh sau nhiều lần gặp gỡ Khách hàng.
Cửa hàng mỹ phẩm online Flipper cần xây dựng một cơ sở dữ liệu để quản
lý kinh doanh mỹ phẩm. Cửa hàng bán nhiều loại mỹ phẩm khác nhau
như sữa rửa mặt, mask, toner… Mỗi loại sản phẩm có những thông tin
như mã loại, tên loại và có nhiều sản phẩm. Mỗi sản phẩm có các thông
tin như mã sản phẩm, tên sản phẩm, giá bán, giá gốc. Mỗi sản phẩm thuộc
một thương hiệu (Mac, La Roche Posay, Vaseline, Lipice...) có các thông
tin như mã thương hiệu, tên thương hiệu. Mỗi đơn hàng có các thông tin
như mã đơn hàng, tổng tiền, ngày đặt, trạng thái đơn hàng, người đặt,
người kiểm tra đơn hàng và chi tiết đơn hàng chứa nhiều sản phẩm gồm
các thông tin mã sản phẩm, mã đơn hàng, số lượng sản phẩm đã đặt. Mỗi
đơn hàng có thể áp dụng một phiếu giảm giá gồm các thông tin như mã
giảm giá, giá trị (theo đơn vị đồng), hạn sử dụng, tình trạng sử dụng. Mỗi
đơn vị giao hàng vận chuyển nhiều đơn hàng và có các thông tin như mã
đơn vị vận chuyển, tên đơn vị vận chuyển. Người dùng có các thông tin
mã người dùng, họ và tên, tên đăng nhập, mật khẩu, số điện thoại, địa chỉ,
trạng thái, loại người dùng. Hệ thống có hai loại người dùng: khách hàng
và người kiểm tra đơn hàng. ! ! ! !
Lecturer: Ly Thi Huyen Chau Page: 4 ! TEAM: 04 Version: 0.2 Date: 08 Jan, 2024 ! CHAPTER 2: ERD
* Dùng Microsoft Office Visio/Draw.io để vẽ và dán hình vào đây (có thể dùng
công cụ khác nếu biết) Chỗ!này!chưa!đúng!! ê bắ h ớ !
CHAPTER 3: BUSINESS CONSTRAINT
* Sinh viên liệt kê tất cả những ràng buộc nghiệp vụ của hệ thống này. Ví dụ: !
Lecturer: Ly Thi Huyen Chau Page: 5 ! TEAM: 04 Version: 0.2 Date: 08 Jan, 2024 !
1. Mỗi đơn hàng chỉ của 1 khách hàng
2. Đơn hàng có thể áp dụng mã giảm giá hoặc không có mã giảm giá
3. Mỗi sản phẩm có 1 mã sản phẩm duy nhất
4. Một khách hàng có thể có nhiều đơn hàng
5. Đơn hàng từ 500k trở lên được free ship
6. Mỗi đơn hàng chỉ được áp dụng 1 phiếu giảm giá
7. Đơn hàng phải có ít nhất 1 sản phẩm
8. Mỗi đơn hàng chỉ do 1 đơn vị vận chuyển giao
9. Mỗi sản phẩm chỉ thuộc một thương hiệu
10. Số điện thoại của khách hàng không được trùng với tài khoản khác
11. Địa chỉ khách hàng là bắt buộc
12. Số lượng đặt của sản phẩm trong chi tiết đơn hàng phải lớn hơn 0
13. Sản phẩm nếu có giá gốc thì phải lớn hơn hoặc bằng giá bán
14. Sản phẩm phải thuộc một loại sản phẩm
15. Mỗi sản phẩm phải có thương hiệu ! ! ! !
CHAPTER 4: DATABASE SCHEMA
* Liệt kê tất cả những Lược đồ quan hệ có thể có tại đây
1) LOAISANPHAM( Mã loại, Tên lo i ) ạ !
Lecturer: Ly Thi Huyen Chau Page: 6 ! TEAM: 04 Version: 0.2 Date: 08 Jan, 2024 !
2) SANPHAM( Mã sản ph m, Tên s ẩ
ản ph m, Giá bán, Giá g ẩ c, Mã lo ố ại, Mã thương hiệu )
3) THUONGHIEU( Mã th ng hi ươ ệu, Tên th ng hi ươ ệu ) 4) CHITIETDONHANG( S l ố ng ượ
đặt, Mã sản ph m, Mã ẩ n hàng) đơ
5) DONHANG( Mã n hàng, Ngày đơ đặt, Tr ng thái, T ạ ổng tiền, Mã ng i dùng, Mã ườ đơn vị ậ v n chuyển)
6) DONVIVANCHUYEN( Mã đơn vị v n chuy ậ
ển, Tên đơn vị v n chuy ậ ển )
7) PHIEUGIAMGIA( Mã phiếu gi m giá, Mã code, Tình tr ả ng s ạ
ử dụng, Hạn sử dụng, Giá trị )
8) NGUOIDUNG( Mã ng i dùng, Tên ườ đăng nh p, H ậ và tên, M ọ t kh ậ u, Tr ẩ ạng thái,
Loại người dùng, Địa chỉ, S ố điện tho i ) ạ
CHAPTER 5: LOGIC DATA DIAGRAM
* Sinh viên Mô tả Siêu dữ liệu tại đây, lưu ý: Khóa chính và Khóa ngoại. 1. Bảng LOAISANPHAM !
Lecturer: Ly Thi Huyen Chau Page: 7 ! TEAM: 04 Version: 0.2 Date: 08 Jan, 2024 ! STT Tên tắt Diễn giải Miền giá trị Ghi chú 1 MALOAI Mã loại VARCHAR(10) NOT NULL 2 TENLOAI Tên loại NVARCHAR(100) NOT NULL 2. Bảng SANPHAM STT Tên tắt Diễn giải Miền giá trị Ghi chú 1 MASP Mã sản phẩm VARCHAR(10) NOT NULL 2 TENSP Tên sản phẩm NVARCHAR(100) NOT NULL 3 GIABAN Giá bán INT NOT NULL 4 GIAGOC Giá gốc INT NULL Giá gốc dùng để áp dụng chương trình giảm giá bằng cách nhập lớn hơn giá bán 5 MALOAI Mã loại VARCHAR(10) NOT NULL 6 MATH Mã thương hiệu VARCHAR(10) NOT NULL 3. Bảng THUONGHIEU STT Tên tắt Diễn giải Miền giá trị Ghi chú 1 MATH Mã thương hiệu VARCHAR(10) NOT NULL 2 TENTH Tên thương hiệu NVARCHAR(100) NOT NULL !
Lecturer: Ly Thi Huyen Chau Page: 8 ! TEAM: 04 Version: 0.2 Date: 08 Jan, 2024 ! 4. Bảng DONHANG STT Tên tắt Diễn giải Miền giá trị Ghi chú 1 MADONHANG Mã đơn hàng VARCHAR(10) NOT NULL 2 NGAYDAT Ngày đặt hàng DATETIME NOT NULL 3 TONGTIEN Tổng tiền INT NOT NULL 4 TRANGTHAI Trạng thái đơn hàng VARCHAR(20) NOT NULL Trạng thái sẽ có 4 giá trị ‘CHUAXULY’, ‘DANGGIAO’, ‘THANHCON G’, ‘HUYDON; 5 MAPHGG Mã phiếu giảm giá VARCHAR(10) NULL Khách hàng có thể áp dụng phiếp giảm giá trên đơn hàng của mình 6 MANGUOIDUNG Mã khách hàng VARCHAR(10) NOT NULL 7 MADVVC Mã đơn vị vận VARCHAR(10) NOT NULL chuyển
5. Bảng CHITIETDONHANG STT Tên tắt Diễn giải Miền giá trị Ghi chú 1 MADONHANG Mã đơn hàng VARCHAR(10) NOT NULL 2 MASANPHAM Mã sản phẩm VARCHAR(10) NOT NULL 3 SOLUONG Số lượng sản phẩm INT NOT NULL
6. Bảng DONVIVANCHUYEN !
Lecturer: Ly Thi Huyen Chau Page: 9 ! TEAM: 04 Version: 0.2 Date: 08 Jan, 2024 ! STT Tên tắt Diễn giải Miền giá trị Ghi chú 1 MADVVC Mã đơn vị vận VARCHAR(10) NOT NULL chuyển 2 TENDVVC Tên đơn vị vận NVARCHAR(100) NOT NULL chuyển 7. Bảng PHIEUGIAMGIA STT Tên tắt Diễn giải Miền giá trị Ghi chú 1 MAPHGG Mã phiếu giảm giá VARCHAR(10) NOT NULL 2 CODE Code giảm giã VARCHAR(10) NOT NULL Mã dùng để người dùng nhập khi thanh toán đơn hàng 3 GIATRI
Giá trị phiếu giảm giá INT NOT NULL Tính theo đơn vị VND 4 HANSUDUNG Hạn sử dụng DATETIME NOT NULL 5 TINHTRANG Tình trạng sử dụng VARCHAR(20) NOT NULL Có 2 giá trị ‘CHUASUDU NG’, ‘DASUDUNG’ 8. Bảng NGUOIDUNG STT Tên tắt Diễn giải Miền giá trị Ghi chú 1 MANGUOIDUNG Mã người dùng VARCHAR(10) NOT NULL 2 TENDANGNHAP Tên đăng nhập VARCHAR(32) NOT NULL 3 MATKHAU Mật khẩu VARCHAR(255) NOT NULL Chứa chuỗi mật khẩu đã được mã hóa !
Lecturer: Ly Thi Huyen Chau Page: 10 ! TEAM: 04 Version: 0.2 Date: 08 Jan, 2024 ! 4 SDT Số điện thoại INT NOT NULL 5 HOTEN Họ và tên NVARCHAR(50) NOT NULL 6 LOAI Loại người dùng VARCHAR(20) NOT NULL Có 2 giá trị ‘NGUOIDUNG ’ và ‘QUANTRI’ 7 DIACHI Địa chỉ VARCHAR(100) NOT NULL 8 TRANGTHAI Trạng thái tài khoản VARCHAR(20) NOT NULL MACó 2 giá trị ‘HOATDONG’ và ‘KHOA’
CHAPTER 6: PHYSICAL DATA DIAGRAM
* Sinh viên dùng chức năng Database Diagrams của SQL Server để thiết kế nên mô hình này: !
Lecturer: Ly Thi Huyen Chau Page: 11 ! TEAM: 04 Version: 0.2 Date: 08 Jan, 2024 ! PHIEUGIAMGIA MAPHGG CODE GIATRI CHITIETDON H ANG HANSUDUNG MADONHANG TINHTRANG MASANPHAM SOLUONG FK_CHITIETDONHANG_DONHAN FK DONHANG PHIEUGIAMGI FK CHITIETDONHANG SANPHAM DONH AN G N GUO ID UN G SAN PHAM MADONHANG MANGUOIDUNG MASP NGAYDAT TENDANGNHAP TENSP LOAISA N PH AM TONGTIEN MATKHAU GIABAN MALOAI TRANGTHAI SDT FK_DONHANG_NGUOIDUNG GIAGOC FK SANPHAM LOAISANPHAM TENLOAI MAPHGG HOTEN MALOAI MANGUOIDUNG LOAI MATH MADVVC DIACHI TRANGTHAI FK SANPHAM THUONGHIEU FK DONHANG DONVIVANCHUYE THUONGHIEU MATH DONVIVAN CH UYEN TENTH MADVVC TENDVVC !
Lecturer: Ly Thi Huyen Chau Page: 12 ! TEAM: 04 Version: 0.2 Date: 08 Jan, 2024 !
CHAPTER 7: SQL PROGRAMMING
* Sinh viên giả lập ra 3 câu hỏi truy vấn phù hợp với đề tài nhóm đang thực hiện
và sử dụng MS SQL Server trên Database của nhóm để viết truy vấn thực hiện 3
câu hỏi đó, sinh viên chụp lại màn hình truy vấn và kết quả thực hiện.
Lưuý: đây chỉ là 3 ví dụ không phải đán án đúng, ví dụ để sinh viên hình dung cách nộp bài.
Câu 1: Hãy cho biết số lượng hoá đơn đặt món “Broken Rice” và tổng tiền trung
bình của những hoá đơn đó có trạng thái là đã thanh toán và trong năm 2024
SELECT COUNT(DISTINCT CASE WHEN D.DISH_NAME = 'BROKEN
RICE' THEN O.ORDER_ID END) AS BEST_DISH,
AVG(CASE WHEN YEAR(O.ORDER_DATE)=2024 THEN O.GRAND_TOTAL END) AS AVG_SALE FROM ((ORDER_T O
INNER JOIN ORDER_DETAIL_T L ON O.ORDER_ID=L.ORDER_ID)
INNER JOIN MENU_T M ON L.MENU_ID=M.MENU_ID)
INNER JOIN DISH_T D ON M.DISH_ID = D.DISH_ID WHERE O.STATUS = 'PAID'
Kết quả thực hiện Câu 1: !
Lecturer: Ly Thi Huyen Chau Page: 13 ! TEAM: 04 Version: 0.2 Date: 08 Jan, 2024 !
Câu 2: Hãy cho biết số lượng hoá đơn đặt món “Broken Rice” và tổng tiền trung
bình của những hoá đơn đó có trạng thái là đã thanh toán và trong năm 2024
SELECT COUNT(DISTINCT CASE WHEN D.DISH_NAME = 'BROKEN
RICE' THEN O.ORDER_ID END) AS BEST_DISH,
AVG(CASE WHEN YEAR(O.ORDER_DATE)=2024 THEN O.GRAND_TOTAL END) AS AVG_SALE FROM ((ORDER_T O
INNER JOIN ORDER_DETAIL_T L ON O.ORDER_ID=L.ORDER_ID)
INNER JOIN MENU_T M ON L.MENU_ID=M.MENU_ID)
INNER JOIN DISH_T D ON M.DISH_ID = D.DISH_ID WHERE O.STATUS = 'PAID'
Kết quả thực hiện Câu 2:
Câu 3: Hãy cho biết số lượng hoá đơn đặt món “Broken Rice” và tổng tiền trung
bình của những hoá đơn đó có trạng thái là đã thanh toán và trong năm 2024
SELECT COUNT(DISTINCT CASE WHEN D.DISH_NAME = 'BROKEN
RICE' THEN O.ORDER_ID END) AS BEST_DISH,
AVG(CASE WHEN YEAR(O.ORDER_DATE)=2024 THEN O.GRAND_TOTAL END) AS AVG_SALE !
Lecturer: Ly Thi Huyen Chau Page: 14 ! TEAM: 04 Version: 0.2 Date: 08 Jan, 2024 ! FROM ((ORDER_T O
INNER JOIN ORDER_DETAIL_T L ON O.ORDER_ID=L.ORDER_ID)
INNER JOIN MENU_T M ON L.MENU_ID=M.MENU_ID)
INNER JOIN DISH_T D ON M.DISH_ID = D.DISH_ID WHERE O.STATUS = 'PAID'
Kết quả thực hiện Câu 3: !
Lecturer: Ly Thi Huyen Chau Page: 15