Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- create database QuanLyThuVien
- use QuanLyThuVien
- create table DocGia
- (
- madg nchar(10),
- hoten nvarchar(40),
- socmnd int,
- ngaysinh date,
- gioitinh nvarchar(5),
- email nvarchar(40),
- diachi nvarchar(40),
- matkhau nvarchar(40),
- primary key(madg)
- )
- create table DauSach
- (
- isbn nchar(10),
- tensach nvarchar(40),
- tacgia nvarchar(40),
- namxb date,
- nhaxb nvarchar(40),
- soluong int,
- mucgiaphat int,
- theloai nvarchar(40),
- primary key(isbn)
- )
- create table CuonSach
- (
- isbn nchar(10),
- masach nchar(10),
- tinhtrang nvarchar(40),
- primary key(isbn,masach)
- )
- create table PhieuMuon
- (
- mapm nchar(10),
- madg nchar(10),
- ngaymuon date,
- primary key(mapm)
- )
- create table CT_PhieuMuon
- (
- mapm nchar(10),
- isbn nchar(10),
- masach nchar(10),
- songayquydinh int,
- primary key(mapm,isbn,masach)
- )
- create table PhieuTra
- (
- mapt nchar(10),
- mapm nchar(10),
- ngaytra date,
- primary key(mapt)
- )
- create table CT_PhieuTra
- (
- mapt nchar(10),
- isbn nchar(10),
- masach nchar(10),
- mucgiaphat int,
- tienphat int,
- primary key(mapt,isbn,masach)
- )
- alter table PhieuMuon add CONSTRAINT FK_PhieuMuon_DocGia FOREIGN KEY (madg) REFERENCES DocGia(madg)
- --alter table CT_PhieuMuon add CONSTRAINT FK_CT_PhieuMuon_DauSach FOREIGN KEY (isbn) REFERENCES DauSach(isbn)
- alter table CT_PhieuMuon add CONSTRAINT FK_CT_PhieuMuon_CuonSach FOREIGN KEY (isbn,masach) REFERENCES CuonSach(isbn,masach)
- alter table CT_PhieuMuon add CONSTRAINT FK_CT_PhieuMuon_PhieuMuon FOREIGN KEY (mapm) REFERENCES PhieuMuon(mapm)
- alter table CT_PhieuTra add CONSTRAINT FK_CT_PhieuTra_PhieuTra FOREIGN KEY (mapt) REFERENCES PhieuTra(mapt)
- --alter table CT_PhieuTra add CONSTRAINT FK_CT_PhieuTra_DauSach FOREIGN KEY (isbn) REFERENCES DauSach(isbn)
- alter table CT_PhieuTra add CONSTRAINT FK_CT_PhieuTra_CuonSach FOREIGN KEY (isbn,masach) REFERENCES CuonSach(isbn,masach)
- alter table CuonSach add CONSTRAINT FK_CuonSach_DauSach FOREIGN KEY (isbn) REFERENCES DauSach(isbn)
- --BẢNG DỌC GIẢ--
- Insert into DocGia values ('1',N'Doc Co Cau Bai',0001,CAST('01/01/1993'as date ),N'Nam',N'Long An','dccb@yahoo.com','001A')
- Insert into DocGia values ('2', N'Join Cena',0002,CAST ('03/01/1991' as date ),N'Nam',N'Vĩnh Long','cena@yahoo.com','002A')
- Insert into DocGia values ('3', N'Super Man',0003,CAST ('09/12/2000' as date ),N'Nam',N'Cà Mau','spm@yahoo.com','003A')
- Insert into DocGia values ('4', N'Quách Tĩnh',0004,CAST( '02/03/1999' as date ),N'Nam',N'Hà Giang','quachtinh@yahoo.com','004A')
- Insert into DocGia values ('5', N'Hoàng Dung',0005,CAST( '01/09/1963' as date ),N'Nữ',N'Nha Trang','hd@yahoo.com','005A')
- Insert into DocGia values ('6', N'Trương Tam Phong',0006,CAST( '11/01/1951'as date ),N'Nam',N'Hải Phòng','t3p@yahoo.com','006A')
- Insert into DocGia values ('7', N'Trương Vô Kị',0007,CAST( '01/12/2003'as date ),N'Nam',N'Rạch Giá','tvk@yahoo.com','007A')
- Insert into DocGia values ('8', N'Bao Công',0008,CAST( '02/04/1959' as date ),N'Nam',N'Chau Phi','baocong@yahoo.com','008A')
- --dinh dang: mm/dd/yyyy
- --BẢNG DẤU SÁCH--
- Insert into DauSach values ('1', N'Truyện Ma','Thanh Tam',CAST( '01/01/1903' as date ),N'Kim Đồng',30,25000,N'Khoa học cơ bản')
- Insert into DauSach values ('2', N'Happy','Le Khoi', CAST('01/12/1943' as date ),N'Kim Đồng',45,15000,N'Khoa học ứng dụng')
- Insert into DauSach values ('3', N'War','Mike Nguyen',CAST ('04/03/1953'as date ),N'Kim Đồng',30,25000,N'Xã hội')
- Insert into DauSach values ('4', N'Truyện Hài','Thanh Tam',CAST( '01/12/1973'as date),N'USA',40,75000,N'Ngoại ngữ')
- Insert into DauSach values ('5', N'Harry Porter','Minh Trung',CAST( '12/11/1933' as date),N'AAA',95,44000,N'Khoa học cơ bản')
- --BẢNG CUON SACH--
- Insert into CuonSach values ('1', '1',N'Đang được mượn')
- Insert into CuonSach values ('2', '3',N'Đang được mượn')
- Insert into CuonSach values ('3', '5',N'Có thể cho mượn')
- Insert into CuonSach values ('4', '2',N'Đang được mượn')
- --BẢNG PHIẾU MƯỢN--
- Insert into PhieuMuon values ('1', '1',cast ('12/04/2016' as date))
- Insert into PhieuMuon values ('2', '2',cast('01/04/2016' as date))
- Insert into PhieuMuon values ('3', '3',cast('12/03/2016' as date))
- Insert into PhieuMuon values ('4', '4',cast('12/09/2015' as date))
- Insert into PhieuMuon values ('5', '5',cast('12/07/2016' as date))
- --BẢNG CT PHIẾU MƯỢN--
- Insert into CT_PhieuMuon values ('1', '1','1',3)
- Insert into CT_PhieuMuon values ('2', '2','2',6)
- Insert into CT_PhieuMuon values ('3', '3','3',12)
- Insert into CT_PhieuMuon values ('4', '4','4',5)
- --BẢNG PHIẾU TRA--
- Insert into PhieuTra values ('1', '1',cast('12/04/2016'as date))
- Insert into PhieuTra values ('2', '2',cast('12/01/2016' as date))
- Insert into PhieuTra values ('3', '3',cast('12/03/2016' as date))
- Insert into PhieuTra values ('4', '4',cast('11/09/2015' as date))
- Insert into PhieuTra values ('5', '5',cast('10/07/2016' as date))
- --BẢNG CT PHIẾU TRA--
- Insert into CT_PhieuTra values ('1', '1','1',25000,5000)
- Insert into CT_PhieuTra values ('2', '2','2',15000,6000)
- Insert into CT_PhieuTra values ('3', '3','3',25000,7000)
- Insert into CT_PhieuTra values ('4', '4','4',75000,9000)
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement