Untitled
unknown
plain_text
a month ago
3.0 kB
1
Indexable
Never
CREATE DATABASE QUANLYNHASACH GO USE QUANLYNHASACH CREATE TABLE CHITIETHOADON( SoHoaDon nvarchar(5), MaSach nvarchar(5), SoLuongBan int, DonGiaBan float, primary key(SoHoaDon, MaSach) ); CREATE TABLE DANHMUCSACH( MaSach nvarchar(5) primary key, TenSach nvarchar(50), TheLoai nvarchar(50), TacGia nvarchar(50), SoLuongTon int ); CREATE TABLE HOADONBANSACH( SoHoaDon nvarchar(5) primary key, MaKhachHang nvarchar(5), NgayLapHoaDon datetime, TriGiaCuaHoaDon float ); CREATE TABLE KHACHHANG( MaKhachHang nvarchar(5) primary key, TenKhachHang nvarchar(20), DiaChi nvarchar(50), DienThoai nvarchar(10) ); CREATE TABLE PHIEUNHAPSACH( SoPhieuNhap nvarchar(5) primary key, MaSach nvarchar(5), NgayNhap datetime, SoLuongNhap int ); CREATE TABLE PHIEUTHUTIEN( SoPhieuThu nvarchar(5) primary key, MaKhachHang nvarchar(5), NgayThuTien datetime, SoTienThu float ); ALTER TABLE CHITIETHOADON ADD FOREIGN KEY(SoHoaDon) REFERENCES HOADONBANSACH(SoHoaDon) ON UPDATE CASCADE ON DELETE CASCADE; ALTER TABLE CHITIETHOADON ADD FOREIGN KEY(MaSach) REFERENCES DANHMUCSACH(MaSach) ON UPDATE CASCADE ON DELETE CASCADE; ALTER TABLE HOADONBANSACH ADD FOREIGN KEY(MaKhachHang) REFERENCES KHACHHANG(MaKhachHang) ON UPDATE CASCADE ON DELETE CASCADE; ALTER TABLE PHIEUNHAPSACH ADD FOREIGN KEY(MaSach) REFERENCES DANHMUCSACH(MaSach) ON UPDATE CASCADE ON DELETE CASCADE; ALTER TABLE PHIEUTHUTIEN ADD FOREIGN KEY(MaKhachHang) REFERENCES KHACHHANG(MaKhachHang) ON UPDATE CASCADE ON DELETE CASCADE; INSERT INTO ChiTietHoaDon (SoHoaDon, MaSach, SoLuongBan, DonGiaBan) VALUES ('HD001', 'TH001', 20, 15000), ('HD001', 'TT001', 10, 20000), ('HD002', 'TT002', 10, 30000), ('HD003', 'TT002', 30, 25000); INSERT INTO DanhMucSach (MaSach, TenSach, TheLoai, TacGia, SoLuongTon) VALUES ('VH001', 'Dòng sông xanh', 'Văn thơ', 'Lê Thành Nam', 0), ('TH001', 'Giải tích hâm', 'Toán học', 'Kỳ Nam', 300), ('TT001', 'Lập trình .Net', 'Tin học', 'Lâm Thiện', 150), ('TT002', 'Lập trình Web cơ bản', 'Tin học', 'Kiến Hưng', 100); INSERT INTO HoaDonBanSach (SoHoaDon, MaKhachHang, NgayLapHoaDon, TriGiaCuaHoaDon) VALUES ('HD001', 'KH001', '2003-10-01', 0), ('HD002', 'KH001', '2003-10-05', 0), ('HD003', 'KH002', '2003-10-06', 0); INSERT INTO KhachHang (MaKhachHang, TenKhachHang, DiaChi, DienThoai) VALUES ('KH001', 'Trần Văn Anh', '12 Lê Lợi', '8421756'), ('KH002', 'Nguyễn Hoàng Nam', '2 Nguyễn Kim', '7541254'), ('KH003', 'Cao Học Giả', '3 Tôn Dan', '8421563'); INSERT INTO PhieuNhapSach (SoPhieuNhap, MaSach, NgayNhap, SoLuongNhap) VALUES ('PN001', 'TH001', '2003-09-20', 100), ('PN002', 'TH001', '2003-09-21', 200), ('PN003', 'TT001', '2003-09-20', 150), ('PN004', 'TT002', '2003-09-20', 100); INSERT INTO PhieuThuTien (SoPhieuThu, MaKhachHang, NgayThuTien, SoTienThu) VALUES ('PT001', 'KH001', '2003-09-20', 500000), ('PT002', 'KH002', '2003-09-21', 600000);
Leave a Comment