CREATE DATABASE Materialsystem	/*建項目數據庫Materialsystem*/
USE Materialsystem	/*置Materialsystem為當前數據庫*/

/* 1.職工表，存儲職工信息*/
CREATE TABLE userinfo(
Snum CHAR(11) PRIMARY KEY,
Sname CHAR(20) UNIQUE,
Ssex CHAR(2) CHECK(Ssex in ('男','女')),
Ssdept CHAR(30),
Smajor CHAR(30),
Sclass CHAR(10)
);

/*2.出庫信息表，存儲貨物出庫信息*/
CREATE TABLE Cardinfo(
Cardnum CHAR(10) PRIMARY KEY,
Snum CHAR(11) UNIQUE,
Cardpwd CHAR(20) NOT NULL,
FOREIGN KEY(Snum) REFERENCES userinfo(Snum)
);

/* 3.庫存貨物信息表，存儲倉庫所有貨物的信息*/
CREATE TABLE Bookbaseinfo(
Bookseque INT IDENTITY(1,1),
Booknum CHAR(50) PRIMARY KEY,
Bookname CHAR(50) NOT NULL,
Bookclass INT CHECK(Bookclass in(1,2,3,4)),
State SMALLINT CHECK(State in(0,1))
);

/* 4.出庫表，存儲職工出庫貨物的信息*/
CREATE TABLE Borrowbook(
Borrownum INT IDENTITY(1,1),
Snum CHAR(11),
Booknum CHAR(50),
Bookname CHAR(50),
Bookclass CHAR(20),
Borrowbookdate DATETIME,
Shouldreturnbookdate DATETIME
FOREIGN KEY(Snum) REFERENCES userinfo(Snum),
FOREIGN KEY(Booknum) REFERENCES Bookbaseinfo(Booknum),
PRIMARY KEY(Snum,Booknum)
);

/* 5.入庫表，存儲貨物入庫的信息,和出庫表一起構成貨物的出入庫信息*/
CREATE TABLE Returnbook(
Returnnum INT IDENTITY(1,1),
Snum CHAR(11),
Booknum CHAR(50),
Bookname CHAR(50),
Bookclass CHAR(20),
Returnbookdate DATETIME,
FOREIGN KEY(Snum) REFERENCES userinfo(Snum),
FOREIGN KEY(Booknum) REFERENCES Bookbaseinfo(Booknum),
PRIMARY KEY(Snum,Booknum)
);

/* 6.管理員信息表，存儲管理員的相關信息*/
CREATE TABLE Managerinfo(
Managernum CHAR(10) PRIMARY KEY,
Managername CHAR(20) ,
Managerpwd CHAR(20) NOT NULL
);

/* 7.出庫表，存儲貨物出庫信息*/
CREATE TABLE Borrowregister(
Borrowregisternum INT IDENTITY(1,1),
Snum CHAR(11),
Booknum CHAR(50),
Bookname CHAR(50),
Bookclass CHAR(20),
Registerdate DATETIME,
FOREIGN KEY(Snum) REFERENCES Cardinfo(Snum),
FOREIGN KEY(Booknum) REFERENCES Bookbaseinfo(Booknum),
PRIMARY KEY(Snum,Booknum)
);

/* 8.入庫表，存儲貨物出庫信息*/
CREATE TABLE Returnregister(
Returnregisternum INT IDENTITY(1,1),
Snum CHAR(11),
Booknum CHAR(50),
Bookname CHAR(50),
Bookclass CHAR(20),
Registerdate DATETIME,
FOREIGN KEY(Snum) REFERENCES Cardinfo(Snum),
FOREIGN KEY(Booknum) REFERENCES Bookbaseinfo(Booknum),
PRIMARY KEY(Snum,Booknum)
);

/*預置職工信息*/
INSERT INTO userinfo VALUES('20050230111','郭小亮','男','電氣工程系','計算機科學與技術','T523-1');
INSERT INTO userinfo VALUES('20050230116','程偉','男','電氣工程系','計算機科學與技術','T523-1');
INSERT INTO userinfo VALUES('20050230112','吳永濤','男','電氣工程系','計算機科學與技術','T523-1');
INSERT INTO userinfo VALUES('20050230117','王旭君','男','電氣工程系','計算機科學與技術','T523-1');
INSERT INTO userinfo VALUES('20050230113','段磊','男','電氣工程系','計算機科學與技術','T523-1');
INSERT INTO userinfo VALUES('20050230114','計金海','男','電氣工程系','計算機科學與技術','T523-1');
INSERT INTO userinfo VALUES('20050230110','宋瑩','男','電氣工程系','計算機科學與技術','T523-1');
INSERT INTO userinfo VALUES('20050230115','秦志偉','男','電氣工程系','計算機科學與技術','T523-1');

/*預置借書卡信息*/
INSERT INTO Cardinfo VALUES('B50180','20050230110','000');
INSERT INTO Cardinfo VALUES('B50181','20050230111','111');
INSERT INTO Cardinfo VALUES('B50182','20050230112','222');
INSERT INTO Cardinfo VALUES('B50183','20050230113','333');
INSERT INTO Cardinfo VALUES('B50184','20050230114','444');
INSERT INTO Cardinfo VALUES('B50185','20050230115','555');
INSERT INTO Cardinfo VALUES('B50186','20050230116','666');
INSERT INTO Cardinfo VALUES('B50187','20050230117','777');

/*預置書庫中的書籍信息*/
INSERT INTO Bookbaseinfo VALUES('ISBN1','Office 2003 教程完全指南',1,1);
INSERT INTO Bookbaseinfo VALUES('ISBN2','Visual C++教程',1,1);
INSERT INTO Bookbaseinfo VALUES('ISBN3','控制工程及信號處理基礎',1,1);
INSERT INTO Bookbaseinfo VALUES('ISBN4','電力電子技術',1,1);
INSERT INTO Bookbaseinfo VALUES('ISBN5','Internet技術與應用',1,1);
INSERT INTO Bookbaseinfo VALUES('ISBN6','計算機圖形學基礎',1,1);
INSERT INTO Bookbaseinfo VALUES('ISBN7','嵌入式系統設計大學教程',1,1);
INSERT INTO Bookbaseinfo VALUES('ISBN8','計算機常用算法與程序設計教程',1,1);
INSERT INTO Bookbaseinfo VALUES('ISBN9','COSPLAY全接觸',2,1);
INSERT INTO Bookbaseinfo VALUES('ISBN10','書法教程',2,1);
INSERT INTO Bookbaseinfo VALUES('ISBN11','廣告創意設計實戰',2,1);
INSERT INTO Bookbaseinfo VALUES('ISBN12','笛子流行金曲99首',2,1);
INSERT INTO Bookbaseinfo VALUES('ISBN13','指舞弦音 民謠吉他教程',2,1);
INSERT INTO Bookbaseinfo VALUES('ISBN14','色彩',2,1);
INSERT INTO Bookbaseinfo VALUES('ISBN15','立體構成',2,1);
INSERT INTO Bookbaseinfo VALUES('ISBN16','計算機藝術設計基礎',2,1);
INSERT INTO Bookbaseinfo VALUES('ISBN17','中國農民藝術',2,1);
INSERT INTO Bookbaseinfo VALUES('ISBN18','吉他三月通',2,1);
INSERT INTO Bookbaseinfo VALUES('ISBN19','素描教程',2,1);
INSERT INTO Bookbaseinfo VALUES('ISBN20','包圍城市 中國農民向城市的遠征',2,1);
INSERT INTO Bookbaseinfo VALUES('ISBN21','北京歡迎你 北京2008年奧運會歌曲集',2,1);
INSERT INTO Bookbaseinfo VALUES('ISBN22','申論寫作專項突破',3,1);
INSERT INTO Bookbaseinfo VALUES('ISBN23','公務員面試專項突破',3,1);
INSERT INTO Bookbaseinfo VALUES('ISBN24','梅德韋杰夫和普京 最高權力的組合',3,1);
INSERT INTO Bookbaseinfo VALUES('ISBN25','大職工黨課教程',3,1);
INSERT INTO Bookbaseinfo VALUES('ISBN26','經濟法要論',3,1);
INSERT INTO Bookbaseinfo VALUES('ISBN27','中國第一保鏢',3,1);
INSERT INTO Bookbaseinfo VALUES('ISBN28','日本外交30年 從福田赳夫到福田康夫',3,1);
INSERT INTO Bookbaseinfo VALUES('ISBN29','2009年全國碩士研究生入學考試歷年真題解析',3,1);
INSERT INTO Bookbaseinfo VALUES('ISBN30','中共黨史重大事件述實',3,1);
INSERT INTO Bookbaseinfo VALUES('ISBN31','大學英語六級考試710分新題型高分攻略',4,1);
INSERT INTO Bookbaseinfo VALUES('ISBN32','心靈燭火 談世間冷暖',4,1);
INSERT INTO Bookbaseinfo VALUES('ISBN33','(全新版) 大學英語綜合教程、聽說教程輔導',4,1);
INSERT INTO Bookbaseinfo VALUES('ISBN34','飄逸而行 談人生',4,1);
INSERT INTO Bookbaseinfo VALUES('ISBN35','光芒童真 人之初',4,1);
INSERT INTO Bookbaseinfo VALUES('ISBN36','午夜玫瑰 談愛情',4,1);
INSERT INTO Bookbaseinfo VALUES('ISBN37','一盒子吻 談親情',4,1);
INSERT INTO Bookbaseinfo VALUES('ISBN38','那時花開 談回憶',4,1);
INSERT INTO Bookbaseinfo VALUES('ISBN39','1000句英文走天下',4,1);
INSERT INTO Bookbaseinfo VALUES('ISBN40','敲響靈魂的晨鐘暮鼓 智慧與哲理',4,1);

/*預置管理員賬號*/
INSERT INTO Managerinfo VALUES('M1000000','張三','000000');
INSERT INTO Managerinfo VALUES('M1000001','李四','000001');




/*以下為Materialsystem數據庫的測試命令*/
USE Materialsystem
USE master
GO

DROP DATABASE Materialsystem
DROP TABLE Borrowbook
DROP TABLE Returnbook
DROP TABLE Borrowregister
DROP TABLE Returnregister

DELETE FROM Borrowbook WHERE Snum='20050230117'
DELETE FROM Borrowbook WHERE Snum='20050230115'
DELETE FROM Bookbaseinfo WHERE Bookclass=1
DELETE FROM Bookbaseinfo WHERE Bookclass=2
DELETE FROM Bookbaseinfo WHERE Bookclass=3
DELETE FROM Bookbaseinfo WHERE Bookclass=4


SELECT * FROM Managerinfo
DELETE FROM Managerinfo WHERE Managernum='M1000001'

SELECT * FROM userinfo


SELECT * FROM Cardinfo


SELECT * FROM Bookbaseinfo