Tin 11: Thực hành Cơ sở dữ liệu kinh doanh trái cây FreshFruit

LÝ THUYẾT CẦN NHỚ: CƠ SỞ DỮ LIỆU & SQL CƠ BẢN

1. Các kiểu dữ liệu thường dùng

- INT: Số nguyên (dùng cho số lượng, mã số tự tăng,...).

- FLOAT / DECIMAL: Số thực (dùng cho tiền tệ, điểm số).

- CHAR(n): Chuỗi ký tự độ dài cố định (dùng cho mã số, ví dụ: CHAR(2)).

- VARCHAR(n): Chuỗi ký tự độ dài thay đổi (dùng cho tên, địa chỉ, ghi chú).

- DATE: Lưu trữ ngày, tháng, năm.

2. Các ràng buộc (Constraints)

- PRIMARY KEY (Khóa chính): Xác định duy nhất một hàng, không được trùng, không được rỗng.

- FOREIGN KEY (Khóa ngoại): Liên kết dữ liệu giữa 2 bảng, tham chiếu đến khóa chính của bảng khác.

- CHECK: Kiểm tra điều kiện dữ liệu nhập vào (ví dụ: \( > 0 \)).

- NOT NULL: Không được phép để trống.

3. Các câu lệnh SQL cơ bản

- CREATE DATABASE: Tạo cơ sở dữ liệu mới.

- CREATE TABLE: Tạo bảng mới.

- ALTER TABLE: Sửa đổi cấu trúc bảng (thêm/sửa/xóa cột hoặc ràng buộc).

- INSERT INTO: Thêm dữ liệu vào bảng.

- SELECT: Truy vấn (lấy) dữ liệu từ bảng.

---

PHIẾU THỰC HÀNH TIN 11

CƠ SỞ DỮ LIỆU KINH DOANH TRÁI CÂY

I. MỤC TIÊU

- Tạo được cơ sở dữ liệu và các bảng có khóa chính, khóa ngoại.

- Thực hiện nhập dữ liệu và truy vấn dữ liệu cơ bản.

- Vận dụng kiến thức để giải quyết bài toán thực tế.

II. BÀI TOÁN

Cửa hàng FreshFruit cần xây dựng CSDL để quản lí việc kinh doanh trái cây gồm: trái cây, nhà cung cấp, khách hàng, hóa đơn và chi tiết hóa đơn.

III. NỘI DUNG THỰC HÀNH & HƯỚNG DẪN GIẢI (SQL)

Câu 1: Tạo CSDL có tên: QL_KINHDOANH_TRAICAY.

Đáp án tham khảo:

CREATE DATABASE QL_KINHDOANH_TRAICAY;
USE QL_KINHDOANH_TRAICAY;

Câu 2: Tạo bảng TRAICAY gồm:

- MaTC (CHAR(2), khóa chính)

- TenTC (VARCHAR(50))

- DonGia (INT, \(>0\))

- DonViTinh (VARCHAR(20))

Đáp án tham khảo:

CREATE TABLE TRAICAY (
    MaTC CHAR(2) PRIMARY KEY,
    TenTC VARCHAR(50),
    DonGia INT CHECK (DonGia > 0),
    DonViTinh VARCHAR(20)
);

Câu 3: Tạo bảng NHACUNGCAP:

- MaNCC (INT, khóa chính)

- TenNCC (VARCHAR(50))

- DienThoai (VARCHAR(15))

- DiaChi (VARCHAR(100))

Đáp án tham khảo:

CREATE TABLE NHACUNGCAP (
    MaNCC INT PRIMARY KEY,
    TenNCC VARCHAR(50),
    DienThoai VARCHAR(15),
    DiaChi VARCHAR(100)
);

Câu 4: Thêm trường MaNCC vào bảng TRAICAY và tạo khóa ngoại liên kết NHACUNGCAP.

Đáp án tham khảo:

-- Thêm cột MaNCC
ALTER TABLE TRAICAY ADD MaNCC INT;

-- Tạo khóa ngoại
ALTER TABLE TRAICAY 
ADD FOREIGN KEY (MaNCC) REFERENCES NHACUNGCAP(MaNCC);

Câu 5: Tạo bảng KHACHHANG:

- MaKH (INT, khóa chính)

- TenKH (VARCHAR(50))

- DienThoai (VARCHAR(15))

Đáp án tham khảo:

CREATE TABLE KHACHHANG (
    MaKH INT PRIMARY KEY,
    TenKH VARCHAR(50),
    DienThoai VARCHAR(15)
);

Câu 6: Nhập dữ liệu:

- Ít nhất 5 trái cây

- 3 nhà cung cấp

- 5 khách hàng

Đáp án tham khảo:

-- Nhập Nhà Cung Cấp trước (vì có khóa ngoại ở bảng Trái Cây)
INSERT INTO NHACUNGCAP VALUES (1, 'Vườn Rau Má', '0901236789', 'Thanh Hóa');
INSERT INTO NHACUNGCAP VALUES (2, 'Nông Trại Việt', '0912345678', 'Tiền Giang');
INSERT INTO NHACUNGCAP VALUES (3, 'TTKT Fruit Plus', '0983654321', 'TP.HCM');

-- Nhập Trái Cây
INSERT INTO TRAICAY VALUES ('T1', 'Táo Mỹ', 60000, 'Kg', 3);
INSERT INTO TRAICAY VALUES ('T2', 'Xoài Cát', 45000, 'Kg', 2);
INSERT INTO TRAICAY VALUES ('T3', 'Nho Đen', 120000, 'Kg', 3);
INSERT INTO TRAICAY VALUES ('T4', 'Dưa Hấu', 15000, 'Kg', 2);
INSERT INTO TRAICAY VALUES ('T5', 'Cam Sành', 30000, 'Kg', 1);

-- Nhập Khách Hàng
INSERT INTO KHACHHANG VALUES (1, 'Nguyễn Văn A', '0909000111');
INSERT INTO KHACHHANG VALUES (2, 'Trần Thị B', '0909000222');
INSERT INTO KHACHHANG VALUES (3, 'Lê Văn C', '0909000333');
INSERT INTO KHACHHANG VALUES (4, 'Phạm Thị D', '0909000444');
INSERT INTO KHACHHANG VALUES (5, 'Hoàng Văn E', '0909000555');

Câu 7: Truy vấn:

1. Hiển thị toàn bộ trái cây.

2. Hiển thị tên trái cây và đơn giá.

3. Trái cây có đơn giá \(> 50000\).

4. Sắp xếp theo đơn giá giảm dần.

Đáp án tham khảo:

-- 7.1
SELECT * FROM TRAICAY;

-- 7.2
SELECT TenTC, DonGia FROM TRAICAY;

-- 7.3
SELECT * FROM TRAICAY WHERE DonGia > 50000;

-- 7.4 (DESC là cách xếp order giảm dần theo order by)
SELECT * FROM TRAICAY ORDER BY DonGia DESC;

Câu 8: Hiển thị tên trái cây và tên nhà cung cấp.

Đáp án tham khảo:

SELECT T.TenTC, N.TenNCC -- gán biến T cho bảng TRAICAY và N cho NHACUNGCAP
FROM TRAICAY T 
JOIN NHACUNGCAP N ON T.MaNCC = N.MaNCC;

Câu 9: Tạo bảng HOADON:

- MaHD (INT, khóa chính)

- MaKH (INT, khóa ngoại)

- NgayBan (DATE)

Đáp án tham khảo:

CREATE TABLE HOADON (
    MaHD INT PRIMARY KEY,
    MaKH INT,
    NgayBan DATE,
    FOREIGN KEY (MaKH) REFERENCES KHACHHANG(MaKH)
);

Câu 10: Tạo bảng CT_HOADON:

- MaHD (INT, khóa ngoại)

- MaTC (khóa ngoại CHAR(2))

- SoLuong (INT, \(>0\))

- Khóa chính ghép: (MaHD, MaTC)

Đáp án tham khảo:

CREATE TABLE CT_HOADON (
    MaHD INT,
    MaTC CHAR(2),
    SoLuong INT CHECK (SoLuong > 0),
    PRIMARY KEY (MaHD, MaTC),
    FOREIGN KEY (MaHD) REFERENCES HOADON(MaHD),
    FOREIGN KEY (MaTC) REFERENCES TRAICAY(MaTC)
);

Câu 11: Truy vấn nâng cao:

1. Tổng số lượng trái cây theo hóa đơn.

2. Tên khách hàng – ngày bán – tổng tiền.

3. Trái cây bán nhiều nhất.

Đáp án tham khảo:

-- Chuẩn bị dữ liệu mẫu cho Hóa Đơn để test
INSERT INTO HOADON VALUES (1, 1, '2023-10-20');
INSERT INTO CT_HOADON VALUES (1, 'T1', 2);
INSERT INTO CT_HOADON VALUES (1, 'T3', 1);

-- 11.1 Tổng số lượng trái cây theo hóa đơn
SELECT MaHD, SUM(SoLuong) AS TongSoLuong 
FROM CT_HOADON 
GROUP BY MaHD;

-- 11.2 Tên khách hàng – ngày bán – tổng tiền
-- Tổng tiền = Số lượng * Đơn giá
SELECT KH.TenKH, HD.NgayBan, SUM(CT.SoLuong * TC.DonGia) AS TongTien
FROM KHACHHANG KH
JOIN HOADON HD ON KH.MaKH = HD.MaKH
JOIN CT_HOADON CT ON HD.MaHD = CT.MaHD
JOIN TRAICAY TC ON CT.MaTC = TC.MaTC
GROUP BY KH.TenKH, HD.NgayBan;

-- 11.3 Trái cây bán nhiều nhất (theo tổng số lượng đã bán)
SELECT TC.TenTC, SUM(CT.SoLuong) AS TongDaBan
FROM TRAICAY TC
JOIN CT_HOADON CT ON TC.MaTC = CT.MaTC
GROUP BY TC.TenTC
ORDER BY TongDaBan DESC LIMIT 1;
-- Lưu ý: Nếu dùng SQL khác thì thay 'LIMIT 1' bằng 'SELECT TOP 1' ở cuối câu lệnh.

IV. KẾT QUẢ

Học sinh lưu file CSDL và nộp theo yêu cầu của giáo viên.