图书管理系统—实验报告

【数据库实施】

1 定义数据库结构及操作

（1）建立“图书表”

CREATE TABLE Book_Inf (

Book_ID int primary key , Book_Name varchar(50) , Book_Authors varchar(50) ,

Book_Style int , Publish_Inf varchar(200) , Content_Inf varchar(200) , Current_Store int , Book_Total int , Book_Price money ,

Enter_Time datetime , Booked int , ) GO

（2）建立“学生表”

CREATE TABLE Students_Inf ( Student_NO int primary key , Student_Name varchar(50) , Student_Card_Num int ,

Sex int , Speciality int , ) GO

（3）建立“管理员表”

CREATE TABLE Manage_Inf (

Manage_NO int PRIMARY KEY , Manage_Name varchar(50), M_password varchar(50) , Workage int, Department int , M_position int, ) GO

（4）建立“预订表” CREATE TABLE [BT_Booked] ( [Student_NO] [int] NOT NULL , [Student_Name] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL , [Book_ID] [int] NULL , [Book_Name] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL , [Booked_Time] [datetime] NULL , //预定时间 [Booked_Condition] [int] NULL , //预定状态

CONSTRAINT [PK_BT_Booked] PRIMARY KEY CLUSTERED ( [Student_NO] ) ON [PRIMARY] , CONSTRAINT [FK__BT_Booked__Book___300424B4] FOREIGN KEY ( [Book_ID] ) REFERENCES [Book_Inf] ( [Book_ID] ), CONSTRAINT [FK__BT_Booked__Stude__2F10007B] FOREIGN KEY ( [Student_NO] ) REFERENCES [Students_Inf] ( [Student_NO] )

) ON [PRIMARY] GO

（5）建立“续借表”

续借表已经和借阅表合并在一起！

（6）建立“借阅表”

CREATE TABLE [BT_Borrowed] ( [Student_NO] [int] NOT NULL , [Student_Name] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL , [Book_ID] [int] NULL , [Book_Name] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL , [Borrowed_Time] [datetime] NULL , //借阅时间 [Return_Time_L] [datetime] NULL , //理论归还时间 [Return_Time] [datetime] NULL , //实际归还时间 [Borrowed_Condition] [int] NULL , //借阅状态 [IsBook_Ag] [int] NULL , //是否预定 [Book_Ag_Time] [datetime] NULL , //续借时间 CONSTRAINT [PK_BT_Borrowed] PRIMARY KEY CLUSTERED ( [Student_NO] ) ON [PRIMARY] , CONSTRAINT [FK__BT_Borrow__Book___36B12243] FOREIGN KEY ( [Book_ID] ) REFERENCES [Book_Inf] ( [Book_ID] ),

CONSTRAINT [FK__BT_Borrow__Stude__35BCFE0A] FOREIGN KEY ( [Student_NO] ) REFERENCES [Students_Inf] ( [Student_NO] )

) ON [PRIMARY] GO

（7）建立“归还表”

归还表已经和借阅表合并在一起！

（8）管理员操作 ①增加学生

INSERT INTO Students_Inf VALUES (2009122143,'ldw',22460,0,502 );

②删除学生

DELETE from Students_Inf

WHERE Student_NO=2009122143;

③修改学生信息

UPDATE Students_Inf

SET Student_Card_Num=22660 WHERE Student_NO=2009122142;

④增加书籍

INSERT INTO Book_Inf VALUES

(05689,'泰戈尔诗集', '泰戈尔',508, '清华出版社', '天上般诗',5,8,$25,'2010/5/10',0);

⑤删除书籍

DELETE from Book_Inf WHERE Book_ID=05689;

⑥修改书籍信息

UPDATE Book_Inf SET Book_Total=6

WHERE Book_ID=05689;

⑦学生借阅图书 CurrentStore int; BookID int;

BookID= Book_ID;

If (CurrentStore=“Select Current_Store From Book_Inf WHERE Book_ID=05941 ”>0) BEGIN

RETURN（“当前图书馆内还有这本书，可以直接借阅”）;

UPDATE Book_Inf SET Current_Store = Current_Store–1 WHERE Book_ID=05941;

INSERT INTO BT_Borrowed

VALUES (2009122142, BookID, '泰戈尔诗集',508, '2011/12/25', '2012/2/25',0,0,’0’) END ELSE

BEGIN

UPDATE Book_Inf SET Current_Store = Current_Store–1 WHERE Book_ID=05941;

INSERT INTO BT_Booked

VALUES (2009122142, BookID, '泰戈尔诗集', '2011/12/25', 0,) END

⑧学生归还图书

Borrowed_Condition int;

If (Borrowed_Condition=“Select Borrowed_Condition From BT_Borrowed WHERE Borrowed_ID =048 ”!=2)

BEGIN

UPDATE Book_Inf SET Current_Store = Current_Store+1 WHERE

Book_ID=05941;

UPDATE BT_Borrowed SET Borrowed_Condition = 1 WHERE Borrowed_ID =048;

END ELSE BEGIN

UPDATE Book_Inf SET Current_Store = Current_Store+1 WHERE

Book_ID=05941;

UPDATE BT_Borrowed SET Borrowed_Condition = 1 WHERE Borrowed_ID =048; RETURN(“您已经逾期！”); END

（9）学生操作 ①预订图书 CurrentStore int; BookID int;

BookID= Book_ID;

If (CurrentStore=“Select Current_Store From Book_Inf WHERE Book_ID=05941 ”>0) BEGIN

RETURN（“当前图书馆内还有这本书，可以直接借阅”）;

UPDATE Book_Inf SET Current_Store = Current_Store–1 WHERE Book_ID=05941;

INSERT INTO BT_Borrowed

VALUES (2009122142, BookID, '泰戈尔诗集',508, '2011/12/25', '2012/2/25',0,0,’0’) END ELSE

BEGIN

UPDATE Book_Inf SET Current_Store = Current_Store–1 WHERE Book_ID=05941;

INSERT INTO BT_Booked

VALUES (2009122142, BookID, '泰戈尔诗集', '2011/12/25', 0,) END

②续借图书

If (Borrowed_Condition=” Select Borrowed_Condition From BT_Borrowed WHERE Borrowed_ID =048”==2)

BEGIN

RETURN(“您借的图书已逾期，请尽快归还！！”); END ELSE

BEGIN

UPDATE BT_Borrowed SET Return_Time_L = NOW.TIME+60 WHERE Borrowed_ID=3210;

END