materialsystem.sql

具有仓库管理信息系统的基本功能。。入库

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