Advertisement
Guest User

Untitled

a guest
Apr 11th, 2016
121
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 5.17 KB | None | 0 0
  1. create database QuanLyThuVien
  2. use QuanLyThuVien
  3. create table DocGia
  4. (
  5. madg nchar(10),
  6. hoten nvarchar(40),
  7. socmnd int,
  8. ngaysinh date,
  9. gioitinh nvarchar(5),
  10. email nvarchar(40),
  11. diachi nvarchar(40),
  12. matkhau nvarchar(40),
  13. primary key(madg)
  14. )
  15. create table DauSach
  16. (
  17. isbn nchar(10),
  18. tensach nvarchar(40),
  19. tacgia nvarchar(40),
  20. namxb date,
  21. nhaxb nvarchar(40),
  22. soluong int,
  23. mucgiaphat int,
  24. theloai nvarchar(40),
  25. primary key(isbn)
  26. )
  27. create table CuonSach
  28. (
  29. isbn nchar(10),
  30. masach nchar(10),
  31. tinhtrang nvarchar(40),
  32. primary key(isbn,masach)
  33.  
  34. )
  35. create table PhieuMuon
  36. (
  37. mapm nchar(10),
  38. madg nchar(10),
  39. ngaymuon date,
  40. primary key(mapm)
  41.  
  42. )
  43. create table CT_PhieuMuon
  44. (
  45. mapm nchar(10),
  46. isbn nchar(10),
  47. masach nchar(10),
  48. songayquydinh int,
  49. primary key(mapm,isbn,masach)
  50. )
  51.  
  52. create table PhieuTra
  53. (
  54. mapt nchar(10),
  55. mapm nchar(10),
  56. ngaytra date,
  57. primary key(mapt)
  58.  
  59. )
  60. create table CT_PhieuTra
  61. (
  62. mapt nchar(10),
  63. isbn nchar(10),
  64. masach nchar(10),
  65. mucgiaphat int,
  66. tienphat int,
  67. primary key(mapt,isbn,masach)
  68. )
  69.  
  70. alter table PhieuMuon add CONSTRAINT FK_PhieuMuon_DocGia FOREIGN KEY (madg) REFERENCES DocGia(madg)
  71. --alter table CT_PhieuMuon add CONSTRAINT FK_CT_PhieuMuon_DauSach FOREIGN KEY (isbn) REFERENCES DauSach(isbn)
  72. alter table CT_PhieuMuon add CONSTRAINT FK_CT_PhieuMuon_CuonSach FOREIGN KEY (isbn,masach) REFERENCES CuonSach(isbn,masach)
  73. alter table CT_PhieuMuon add CONSTRAINT FK_CT_PhieuMuon_PhieuMuon FOREIGN KEY (mapm) REFERENCES PhieuMuon(mapm)
  74. alter table CT_PhieuTra add CONSTRAINT FK_CT_PhieuTra_PhieuTra FOREIGN KEY (mapt) REFERENCES PhieuTra(mapt)
  75. --alter table CT_PhieuTra add CONSTRAINT FK_CT_PhieuTra_DauSach FOREIGN KEY (isbn) REFERENCES DauSach(isbn)
  76. alter table CT_PhieuTra add CONSTRAINT FK_CT_PhieuTra_CuonSach FOREIGN KEY (isbn,masach) REFERENCES CuonSach(isbn,masach)
  77. alter table CuonSach add CONSTRAINT FK_CuonSach_DauSach FOREIGN KEY (isbn) REFERENCES DauSach(isbn)
  78.  
  79.  
  80. --BẢNG DỌC GIẢ--
  81. 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')
  82. 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')
  83. Insert into DocGia values ('3', N'Super Man',0003,CAST ('09/12/2000' as date ),N'Nam',N'Cà Mau','spm@yahoo.com','003A')
  84. 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')
  85. 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')
  86. 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')
  87. 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')
  88. 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')
  89. --dinh dang: mm/dd/yyyy
  90.  
  91. --BẢNG DẤU SÁCH--
  92. 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')
  93. 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')
  94. Insert into DauSach values ('3', N'War','Mike Nguyen',CAST ('04/03/1953'as date ),N'Kim Đồng',30,25000,N'Xã hội')
  95. 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ữ')
  96. 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')
  97. --BẢNG CUON SACH--
  98. Insert into CuonSach values ('1', '1',N'Đang được mượn')
  99. Insert into CuonSach values ('2', '3',N'Đang được mượn')
  100. Insert into CuonSach values ('3', '5',N'Có thể cho mượn')
  101. Insert into CuonSach values ('4', '2',N'Đang được mượn')
  102. --BẢNG PHIẾU MƯỢN--
  103. Insert into PhieuMuon values ('1', '1',cast ('12/04/2016' as date))
  104. Insert into PhieuMuon values ('2', '2',cast('01/04/2016' as date))
  105. Insert into PhieuMuon values ('3', '3',cast('12/03/2016' as date))
  106. Insert into PhieuMuon values ('4', '4',cast('12/09/2015' as date))
  107. Insert into PhieuMuon values ('5', '5',cast('12/07/2016' as date))
  108. --BẢNG CT PHIẾU MƯỢN--
  109. Insert into CT_PhieuMuon values ('1', '1','1',3)
  110. Insert into CT_PhieuMuon values ('2', '2','2',6)
  111. Insert into CT_PhieuMuon values ('3', '3','3',12)
  112. Insert into CT_PhieuMuon values ('4', '4','4',5)
  113. --BẢNG PHIẾU TRA--
  114. Insert into PhieuTra values ('1', '1',cast('12/04/2016'as date))
  115. Insert into PhieuTra values ('2', '2',cast('12/01/2016' as date))
  116. Insert into PhieuTra values ('3', '3',cast('12/03/2016' as date))
  117. Insert into PhieuTra values ('4', '4',cast('11/09/2015' as date))
  118. Insert into PhieuTra values ('5', '5',cast('10/07/2016' as date))
  119.  
  120. --BẢNG CT PHIẾU TRA--
  121. Insert into CT_PhieuTra values ('1', '1','1',25000,5000)
  122. Insert into CT_PhieuTra values ('2', '2','2',15000,6000)
  123. Insert into CT_PhieuTra values ('3', '3','3',25000,7000)
  124. Insert into CT_PhieuTra values ('4', '4','4',75000,9000)
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement