Đề tài Thiết kế cơ dở dữ liệu cho Quản lý bán háng của một siêu thị

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}