nhằm giúp các bạn sinh viên trong việc làm bài tập lớn đồ án tốt nghiệp
tôi đưa lên bài đề tài quản lý siêu thị đã được phê duyệt cho các bạn tham khảo
16 trang |
Chia sẻ: lvcdongnoi | Lượt xem: 3386 | Lượt tải: 2
Bạn đang xem nội dung tài liệu Đề tài Quản lý bán háng của một siêu thị, để tải tài liệu về máy bạn click vào nút DOWNLOAD ở trên
BÀI TÂP LỚN:
CƠ SỞ DỮ LIỆU
ĐỀ TÀI:
QUẢN LÝ BÁN HÁNG CỦA MỘT SIÊU THỊ
GIÁO VIÊN HƯỚNG DẪN: HOÀNG THỊ HÀ
NHÓM SINH VIÊN THỰC HIỆN:
NGUYỄN VĂN TIẾN
NGUYỄN THỊ THANH PHÚC
NGUYỄN THỊ HUỀ
LÊ MẠNH HÀ
LỚP : THAK51.
KHOA: CÔNG NGHỆ THÔNG TIN
PHẦN MỞ ĐẦU
Cho một cơ sở dữ liệu dùng để quản lý việc mua bán và đặt hàng giao hang của siêu thị. đồ cơ sở dữ liệu bao gồm các lược đồ quan hệ như sau:
+ NHAN VIEN: tập thưc thể bao gồm các nhân viên làm việc trong siêu thị, mỗi nhân viên có một mã nhân viên (MANV) duy nhất, mỗi MANV xác định một tên nhân viên (TENNV), ngay sinh (NS), que quan (QQ), số điện thoại (TEL), lương (LUONG). Mỗi nhân viên có thể làm việc ở nhiều gian hang, mỗi nhà quản lý thì có thể quản lý nhiều nhân viên.
+ NGUOI QUAN LY: người quản lý gồm các trưởng phòng trong siêu thị. Mỗi người quản lý có một mã của người quản lý (MANQL) duy nhất, mỗi MANQL xác định một tên người quản lý (TENNQL), địa chỉ (DIACHI).
+ GIAN HANG: gồm tập thực thể các gian hang trong siêu thị. Mỗi gian hang có một mã gian hang (MAGH) duy nhất, mỗi MAGH xác định một tên gian hang (TENGH) và vị trí (VITRI). Mỗi người quản lý thì có thể quản lý nhiều gian hang.
+ MAT HANG: gồm tập thực thể các mặt hang bán trong siêu thị. Mỗi mặt hàng có một mã mặt hàng (MAMH) duy nhất, mỗi MAMH xác định một tên mặt hang (TENMH), số lượng (SL), ngày sản xuất (NSX). Mỗi gian hang thì có thể chứa nhiều mặt hàng.
+ NHA CUNG CAP: gồm tập thực thể những người cung cấp các mặt hang cho siêu thị. Mỗi nhà cung cấp có một mã nhà cung cấp (MANCC) duy nhất, mỗi MANCC xác định duy nhất một tên nhà cung cấp (TENNCC), địa chỉ (DC), số điện thoại (TEL).
+ HOA DON: tập thực thể gồm các đơn đặt hàng của khách hang. Mỗi hoá đơn có một mã số duy nhất là MAHD, mỗi hoá dơn bán hang có thể gồm nhiều mặt hang. Mỗi hoá đơn xác định ngày lập hoá đơn (NGAY), giá mặt hang (GIAMH). Mỗi khách hang trong một ngày có thể có nhiều lần mua hang nên có nhiều hoá đơn, và mỗi nhân viên có thể quản lý thu tiền của nhiều hoá đơn.
+ KHACH HANG: tập thực thể các khách hang của siêu thị. Mỗi khách hang có một mã khách hang (MAKH) duy nhất, mỗi MAKH xác định một tên khách hang (TENKH) và địa chỉ (ĐC).
+ CHI TIET: Mỗi MAHD và MAMH xác định số lượng bán (SL) của mỗi mặt hàng trong một hoá đơn.
+ CUNG CAP: Mỗi MANCC và MAMH xác định giá bán (GIA) của mỗi mặt hang.
Mô hình thực thể với các thuộc tính dưới dạng bảng sau:
1.NHÀ CUNG CẤP.
STT
THUỘC TÍNH
DIỄN GIẢI
1
MANCC
MÃ NHÀ CUNG CẤP
2
TENNCC
TÊN NHÀ CUNG CẤP
3
SĐT
SỐ DIỆN THOẠI
4
DIACHI
ĐỊA CHỈ
2.MẶT HÀNG
STT
THUỘC TÍNH
DIỄN GIẢI
1
MAMH
MÃ MẶT HÀNG
2
TENMH
TÊN MẶT HÀNG
3
SOLUONG
SỐ LƯỢNG
4
NSX
NGÀY SẢN XUẤT
3.GIAN HÀNG
STT
THUỘC TÍNH
DIỄN GIẢI
1
MAGH
MÃ GIAN HÀNG
2
TENGH
TÊN GIAN HÀNG
3
VỈTI
VỊ TRÍ
4. NHÀ QUẢN LÝ
STT
THUỘC TÍNH
DIỄN GIẢI
1
MANQL
MÃ NGƯỜI QUẢN LÝ
2
TEN
TÊN NGƯỜI QUẢN LÝ
3
DIACHI
ĐỊA CHỈ
5.NHÂN VIÊN.
STT
THUỘC TÍNH
DIỄN GIẢI
1
MANV
MÃ NHÂN VIÊN
2
TEN
TÊN NHÂN VIÊN
3
NGAYSINH
NGÀY SINH
4
QUEQUAN
QUÊ QUÁN
5
SDT
SỐ ĐIỆN THOẠI
6
LUONG
LƯƠNG
6.KHÁCH HÀNG
STT
THUỘC TÍNH
DIỄN GIẢI
1
MAKH
MÃ KHÁCH HÀNG
2
HOTEN
HỌ TÊN
3
DIACHI
ĐỊA CHỈ
7.HOÁ ĐƠN
STT
THUỘC TÍNH
DIỄN GIẢI
1
MAHD
MÃ HOÁ ĐƠN
2
NGAY
NGÀY MUA
3
GIAMH
GIÁ MẶT HÀNG
MÔ HÌNH DỮ LIỆU QUAN HỆ
NGAY
#MAHĐ
#MANCC
SĐT
NSX
TENCC
SL
TENMH
#MAMH
GIAHH
HOÁ ĐƠN
MẶT HÀNG
NHÀ CUNG CẤP
KHÁCH HÀNG
NGƯỜI QUẢN LÝ
GIAN HÀNG
NHÂN VIÊN
SĐT
#MANQL
DIACHI
#MAGH
TENGH
DIACHI
TEN
QUEQUAN
NS
TENNV
#MANV
#MAKH
ĐIACHI
HOTEN
LUONG
ĐIACHI
MÔ HÌNH THỰC THỂ LIÊN KẾT
N
N
N
N
HOÁ
ĐƠN
MẶT
HÀNG
KHÁCH
HÀNG
NHÀ CUNG CẤP
NHÂN
VIÊN
NHÀ QUẢN LÝ
GIAN
HÀNG
LAMVIEC LAMVIEC
QLY
chitiet
MUA
CC
QLY
KT
CHUA
1
1
1
N
1
N
N
N
N
N
1
1
CÂU LỆNH TRUY VẤN SQL
I.Tạo bảng:
+ Create table nhacungcap(mancc text(20) not null primary key,ten text(20),diachi text(20),sdt number)
+ Create table nguoiquanly(maNQL text(20) not null primary key,ten text(20),diachi text(20))
+ Create table gianhang(maGH text(20) not null primary key,tenGH text(20),vitri text(20),maNQL text(20), foreign key (maNQL) references nguoiquanly(maNQL))
+ Create table mathang(maMH text(20) not null primary key,tenMH text(20),soluong text(20),NSX number,maGH text(20),foreign key (maGH) references gianhang(maGH))
+ Create table khachhang(maKH text(20) not null primary key,hoten text(20),diachi text(20))
+ Create table nhanvien(maNV text(20) not null primary key,ten text(20),ngaysinh text(20),quequan text(20),sdt number,luong number,maGH text(20),foreign key (maGH) references gianhang(maGH))
+ Create table hopdong(maHD text(20) not null primary key,ngay number,giaMH text(20),maNV text(20),foreign key (maNV) references nhanvien(maNV),maKH text(20),foreign key (maKH) references khachhang(maKH))
+ Create table chitiet(maHD text(20),maMH text(20),soluong text(20),primary key (maHD,maMH))
+ Create table cungcap(maNCC text(20),maMH text(20), gia text(20), primary key (maNCC,maMH))
II. Viết các câu hỏi sau dưới dạng ngôn ngữ hỏi SQL
+ Xóa trường maNQL có kiểu dữ liệu Text ra khỏi bảng nhanvien đã có:
ALTER TABLE nhanvien DROP COLUMN maNQL text[20]
+ Thêm trường sdt có kiểu dữ liệu number vào bảng nguoiquanly đã có:
ALTER TABLE nguoiquanly ADD COLUMN sdt text[20]
+ Xóa bảng nhacungcap khỏi CSDL đã có:
DROP TABLE nhacungcap
+ Thay đổi kiểu dữ liệu number thành kiểu varchar(20) của trường ngaysinh của bảng nhanvien đã có:
ALTER TABLE nhanvien ALTER COLUMN ngaysinh Varchar[20]
+ Xóa dữ liệu là một bản ghi khỏi bảng nhanvien với các thông tin như sau:
(maNV=54)
DELETE FROM nhanvien
WHERE maNV=’54’
+ Thay đổi tên của nhân viên có maNV=54 thành “Nguyen thi C” của bảng nhanvien đã có:
UPDATE nhanvien
SET ten=’Nguyen thi C’
WHERE maNV=’54’
+Thêm vào bảng nguoiquanly 1 dòng có nội dung:
(maNQL=70
ten=’Tong van A’
diachi=’Lao Cai’)
INSERT INTO nguoiquanly (maNQL,ten,diachi)
VALUES (‘70’,’Tong van A’,’Lao Cai’)
III. PHẦN CÂU HỎI TRUY VẤN SQL
+ Câu 1:Tìm tên nhà cung cấp mặt hàng có mã là 25 .
SELECT nhacungcap.ten
FROM nhacungcap INNER JOIN cungcap ON nhacungcap.maNCC=cungcap.maNCC
WHERE cungcap.maMH='25';
Cau1
ten
Quang Thắng
+ Câu 2:Xem nhà cung cấp có mã là 17 cung cấp mặt hàng có tên là gì.
SELECT mathang.tenMH
FROM cungcap INNER JOIN mathang ON cungcap.maMH=mathang.maMH
WHERE maNCC='17';
Cau2
tenMH
đồ điện
+ Câu 3:In ra danh sách ten,diachi,sdt của các nhà cung cấp cung cấp mặt hàng có mã là 22.
SELECT nhacungcap.ten, nhacungcap.diachi, nhacungcap.sdt
FROM nhacungcap INNER JOIN cungcap ON nhacungcap.maNCC=cungcap.maNCC
WHERE maMH='22';
Cau3
ten
diachi
sdt
Banh Tien Long
ha noi
542154
Cao Thai Son
Hai phong
4842315
+ Câu 4:In ra danh sách tên nhà cung cap có mặt hàng với số lượng trên 50.
SELECT nhacungcap.ten
FROM (nhacungcap INNER JOIN cungcap ON nhacungcap.maNCC=cungcap.maNCC) INNER JOIN mathang ON cungcap.maMH=mathang.maMH
WHERE mathang.soluong >'50';
Cau4
ten
Lam Truong
Quang Thang
+ Câu 5:In ra danh sách nhân viên có lương >1000.
SELECT *
FROM nhanvien
WHERE nhanvien.luong > 1000;
Cau5
maNV
ten
ngaysinh
quequan
sdt
luong
maNQL
maGH
50
Nguyen thi thanh Phu
23/12/88
Nam Dinh
974134965
2000
2
42
51
Nguyen Van Tien
22/4/87
Ha noi
978621052
100000
3
44
52
Nguyen Thi Hue
23/12/88
Nam Dinh
959519872
5000
1
43
54
Pham Bang Bang
25/5/86
trung quoc
959693498
20000
5
46
+ Câu 6:In ra tên , số lượng ngày sản xuất của mặt hàng được mua vào ngày 3
SELECT mathang.tenMH, mathang.soluong, mathang.NSX
FROM mathang INNER JOIN chitiet ON mathang.maMH=chitiet.maMH
WHERE chitiet.maHD in (Select hoadon.maHD
from hoadon
where hoadon.ngay=3);
Cau6
tenMH
soluong
NSX
sữa rửa mặt
12
12
+ Câu 7:Danh sách các khách hàng đã mua các mặt hàng có maMH =21 trong ngày 5. Yêu cầu maKH,hoten,diachi,sdt,tenhang.
SELECT khachhang.maKH, khachhang.hoten, khachhang.diachi, mathang.tenMH
FROM ((khachhang INNER JOIN hoadon ON khachhang.maKH=hoadon.maKH) INNER JOIN chitiet ON hoadon.maHD=chitiet.maHD) INNER JOIN mathang ON chitiet.maMH=mathang.maMH
WHERE mathang.maMH='21' and hoadon.ngay=5;
câu7
maKH
hoten
diachi
tenMH
37
Drogba
bờ biển nga
kem đánh răng
+ Câu 8:Thống kê việc mua hàng trong ngày 5 của maHK=37.Yêu cầu các thông tin maKH họ tên,maHD,trị giá hóa đơn là tổng số tiền trong 1 hóa đơn.(trigiaHD la thuộc tính tự đặt)
SELECT chitiet.maHD, chitiet.maMH, mathang.tenMH, hoadon.giaMH AS TongGia
FROM ((khachhang INNER JOIN hoadon ON khachhang.maKH=hoadon.maKH) INNER JOIN chitiet ON hoadon.maHD=chitiet.maHD) INNER JOIN mathang ON chitiet.maMH=mathang.maMH
WHERE hoadon.ngay=5 and khachhang.maKH='37';
câu8
maHD
maMH
tenMH
TongGia
64
21
kem đánh răng
70000
64
26
mũ
70000
MÔ HÌNH QUAN HỆ
KH(#maKH, hoten, diachi)
HD(#maHD, ngay, giaHD, maNV, maKH)
NV(#maNV, ten, ngaysinh, quequan, sdt, luong, maNQL, maGH)
GH(#maGH, vitri, tenGH, maNQL)
NQL(#maNQL, ten, diachi)
NCC(#maNCC, ten, diachi, sdt)
MH(#maMH, TenMH, soluong, NSX, maGH)
Chitiet(#maHD, #maMH, soluong)
CC(#maNCC, #maMH, gia)
PHỤ THUỘC HÀM
F1={maKH→hoten, diachi}
F2={maHD→ngay, giaHD, maNV, maKH}
F3={maNV→ten, ngaysinh, quequan, sdt, luong, maNQL, maGH, {ten,ngaysinh) →quequan, {ten, ngaysinh}→sdt}
F4={maGH→{vitri, tenGH, maNQL},tenGH→vitri, tenGH→maNQL}
F5={maNQL→ten, diachi}
F6={maNCC→{ten, diachi, sdt}, {diachi, ten}→sdt}
F7={maMH→{tenMH, soluong, NSX, maGH}, tenMH→soluong, tenMH→maGH}
F8={{maHD, maMH}→soluong)
F9={{maNCC, maMH}→gia}
Đề bài:Chuẩn hóa về dạng BCNF
+F1 , F2, F5, F8, F9 đã ở dạng chuẩn BCNF
+chuẩn hóa F3, F4, F6, F7
+ TH1 : F3 chưa ở dạng chuẩn BCNF
ĐẶT maNV=A, ten=B, ngaysinh=C, quequan=D; sdt=E; luong=F; maNQL=G; maGH= H;
F3 khi đó được viết lại là: F3={A→B, A→C, A→D, A→E, A→F, A→G, A→H, BC→D, BC→E}
R(U, F)
U=(ABCDEFGH)
+ Tìm khóa
TN={A} TG={ B, C }
Xi
(TN U Xi)
(TN U Xi)+
Siêu khóa
Khóa
Φ
A
U
A
A
B
AB
U
AB
C
AC
U
AC
BC
ABC
U
ABC
Vậy khóa K=A
Tách lược đồ R thành các lược đồ quan hệ
+ có BC→D vi phạm BCNF. Tách R→R1(BCD) và R2(ABCEFGH)
F1={ BC→D}
F2={A→B, A→C, A→E, A→F, A→G, A→H, BC→E}
Có khóa là A →tách R2 thành R3(BCE) và R4(ABCFGH)
F3={BC→E}
F4={ A→B, A→C, A→F, A→G, A→H} có khoá là A nên F4 đạt chuẩn BCNF
→R=ρ( BCD, BCE, ABCFGH)
+ TH2:F4 chưa ở dạng chuẩn BCNF
đặt maGH=A, vitri=B, tenGH=C; maNQL=D;
F4 được viết lại là:
F4={A→B,A→C,A→D,C→B,C→D}
R(U,F)
U(ABCD)
Tìm khóa
TN={A} TG={C}
Xi
(TN U Xi)
(TN U Xi)+
Siêu khóa
Khóa
Φ
A
U
A
A
C
AC
U
AC
Vậy khóa K=A
Tách lược đồ R thành các lược đồ quan hệ
+ có C→B Vi phạm BCNF. Tách R→R1(BC) và R2(ACD)
F1={ C→B}
F2={ A→C, A→D, C→D} có khoá là A
→tách R2 thành R3(CD) và R4(AC)
F3={C→D}
F4={ A→C} có khoá là A nên F4 đạt chuẩn BCNF
→R=ρ(BC, CD, AC}
+TH3:F6 chưa ở dạng chuẩn BCNF
đặt maNCC=A, ten=B, diachi=C; sdt=D;
Khi đó F6 có dạng
F6 ={A→B, A→C, A→D, BC→D}
R(U,F);
U(ABCD)
+Tìm khóa
TN={A} ; TG={B,C}
Xi
(TN U Xi)
(TN U Xi)+
Siêu khóa
Khóa
Φ
A
U
A
A
B
AB
U
AB
C
AC
U
AC
BC
ABC
U
ABC
Vậy khóa K=A
Tách lược đồ R thành các lược đồ quan hệ
+ có BC→D Vi phạm BCNF. Tách R→R1(BCD) và R2(ABC)
F1={ BC→D}
F2={ A→C} có khoá là A
→tách F2 đạt chuẩn BCNF
→R=ρ( BCD, ABC)
+ TH4:F7 chưa ở dạng chuẩn BCNF
đặt maMH=A; tenMH=B; soluong=C; NSX=D; maGH=E;
Vậy F7 có dạng F7={A→B,A→C,A→D,A→E,B→C}
+Tìm khóa
TN={A}; TG={B}
Xi
(TN U Xi)
(TN U Xi)+
Siêu khóa
Khóa
Φ
A
U
A
A
B
AB
U
AB
Vậy khóa K là A
Tách lược đồ R thành các lược đồ quan hệ
+ có B→C Vi phạm BCNF. Tách R→R1(BC) và R2(ABDE)
F1={ B→C}
F2={ A→B, A→D, A→E } có khoá là A
→ F2 đạt chuẩn BCNF
→R=ρ(BC, ABDE}