Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE DATABASE Lib_Management;
- /* Change to the Lib_Management database */
- /* CREATE TABLE */
- USE Lib_Management;
- GO
- CREATE TABLE dbo.Categories (
- CategoryID CHAR(3) NOT NULL Primary Key,
- Category_Name NVARCHAR(50) NOT NULL,
- Moreinfo NVARCHAR(100)
- );
- CREATE TABLE dbo.Books (
- BookID CHAR(6) NOT NULL PRIMARY KEY ,
- Name_book NVARCHAR(50) NOT NULL,
- Authors NVARCHAR(50) NOT NULL,
- CategoryID CHAR(3) NOT NULL,
- Num_copy INT NOT NULL,
- Foreign Key (CategoryID) REFERENCES dbo.Categories(CategoryID)
- );
- CREATE TABLE dbo.Students (
- UserID CHAR(8) NOT NULL,
- Student_Name VARCHAR(50) NOT NULL,
- "Address" VARCHAR(50) NOT NULL,
- Tel CHAR(10) NOT NULL,
- Primary Key (UserID)
- );
- CREATE TABLE dbo.Slip (
- SlipID CHAR(8) NOT NULL PRIMARY KEY,
- UserID CHAR(8) NOT NULL ,
- BookID CHAR(6),
- DateBorrow DATETIME,
- DateReturn DATETIME NULL,
- "Status" INT,
- FOREIGN KEY (BookID) REFERENCES dbo.Books(BookID),
- FOREIGN KEY (UserID) REFERENCES dbo.Students(UserID)
- );
- CREATE TABLE dbo.Book_has_Slip(
- ID INT IDENTITY(1,1) PRIMARY KEY NOT NULL ,
- SlipID CHAR(8),
- BookID CHAR(6),
- FOREIGN KEY (BookID) REFERENCES Books(BookID),
- FOREIGN KEY (SlipID) REFERENCES dbo.Slip(SlipID)
- );
- -- INSERT DATA INTO TABLE Categories --
- INSERT INTO Categories
- VALUES
- ('CSD','Co so du lieu','Access,Oracle'),
- ('ECO','Ecommerce','sách về thương mại điện tử'),
- ('GTT','Giai thuat','Các bài toán mẫu, các giải thuật, cấu trúc dữ liệu'),
- ('HTT','He thong','Windows, Linux, Unix'),
- ('LTT','Ngon ngu lap trinh','Visual Basic, C, C++, Java'),
- ('PTK','Phan tich va thiet ke','Phân tích và thiết kế giải thuật, hệ thống thông tin v.v..'),
- ('VPP','Van phong','Word,Excel'),
- ('WEB','Web','Javascript, Vbscript,HTML, Flash');
- -- INSERT DATA INTO TABLE Books --
- INSERT INTO Books
- VALUES
- ('CSD001','Cơ sở dữ liệu','Đỗ Trung Tấn','CSD',3),
- ('CSD002','SQL Server 7.0','Elicom','CSD',3),
- ('CSD003','Oracle 8i','Trần Tiến Dung','CSD',5),
- ('HTT001','Windows2000 Professional','Nguyễn Thành Vinh','HTT',3),
- ('HTT002','Windows2000 Advances Server','Dương Văn Chính','HTT',5),
- ('LTT001','Lập trình visual Basic 6','Nguyễn Tiến ','LTT',3),
- ('LTT002','Ngôn ngữ lập trình c++','Tăng Ðình Quí','LTT',5),
- ('LTT003','Lập trình Windows bằng Visual c++','Ðặng Văn Ðức','LTT',4),
- ('VPP001','Excel Toàn tập','Ðoàn Công Hùng','VPP',5),
- ('VPP002','Word 2000 Toàn tập','Ðoàn Công Hùng','VPP',4),
- ('VPP003','Làm kế toán bằng Excel','Vu Duy Sanh','VPP',5),
- ('WEB001','Javascript','Lê Minh Trí','Web',5),
- ('WEB002','HTML','Nguyễn Thị Minh Khoa','Web',3);
- -- INSERT DATA INTO TABLE Students --
- INSERT INTO Students
- VALUES
- ('Vy Văn Kiệt','92 Quang Trung - Đà Nẵng','0511810583'),
- ('Nguyễn Khánh','92 Quang Trung - Đà Nẵng','0511810583'),
- ('Nguyễn Minh Quốc','92 Quang Trung - Đà Nẵng','0511810583'),
- ('Hồ Phước Thôi','92 Quang Trung - Đà Nẵng','0511810583'),
- ('Nguyễn Văn Định','92 Quang Trung - Đà Nẵng','0511810583'),
- ('Nguyễn Văn Hải','92 Quang Trung - Đà Nẵng','0511810583'),
- ('Nguyễn Thị Thuý Hà','92 Quang Trung - Đà Nẵng','0511810583'),
- ('Đỗ Thị Thiên Ngân','92 Quang Trung - Đà Nẵng','0511810583'),
- ('Nguyễn Văn A','30 Phan Châu Trinh - Đà Nẵng','0913576890');
- -- INSERT DATA INTO TABLE Slip --
- INSERT INTO Slip
- VALUES
- ('00000001','CSD001','2014/07/30',NULL,0),
- ('00000002','LTT001','2014/06/30','2014/07/25',1),
- ('00000003','CSD002','2014/08/15',NULL,0),
- ('00000004','LTT003','2014/08/10','2014/08/30',1),
- ('00000005','WEB001','2014/07/10','2014/07/20',1),
- ('00000006','HTT001','2014/08/10',NULL,0),
- ('00000007','HTT002','2014/08/20','2014/08/25',1),
- ('00000008','WEB001','2014/08/30',NULL,0),
- ('00000009','CSD002','2014/08/10','2014/08/15',1),
- ('00000010','WEB002','2014/07/15','2014/07/30',1),
- ('00000011','VPP001','2014/08/30',NULL,0),
- ('00000012','VPP003','2014/08/20','2014/08/25',1),
- ('00000013','VPP001','2014/08/30',NULL,0),
- ('00000014','CSD001','2014/08/20','2014/08/23',1);
- GO
- INSERT INTO Book_has_Slip
- VALUES
- (DEFAULT,'00000001','CSD001'),
- (DEFAULT,'00000003','CSD002'),
- (DEFAULT,'00000002','LTT001'),
- (DEFAULT,'00000004','LTT003'),
- (DEFAULT,'00000005','WEB001'),
- (DEFAULT,'00000006','HTT001'),
- (DEFAULT,'00000007','HTT002'),
- (DEFAULT,'00000008','WEB001'),
- (DEFAULT,'00000009','CSD002'),
- (DEFAULT,'00000010','WEB002'),
- (DEFAULT,'00000011','VPP001'),
- (DEFAULT,'00000012','VPP003'),
- (DEFAULT,'00000013','VPP001'),
- (DEFAULT,'00000014','CSD001');
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement