Untitled

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);