CREATE TABLE [dbo].[读者信息] (
[条形码] [varchar] (40) NULL ,
[编号] [varchar] (20) NOT NULL ,
[姓名] [varchar] (20) NULL ,
[性别] [varchar] (8) NULL ,
[类型] [varchar] (20) NOT NULL ,
[出生日期] [smalldatetime] NULL ,
[有效证件] [varchar] (60) NULL ,
[证件号码] [varchar] (60) NULL ,
[联系方式] [varchar] (100) NULL ,
[登记日期] [smalldatetime] NULL ,
[有限期至] [smalldatetime] NULL ,
[操作员] [varchar] (20) NULL ,
[备注] [varchar] (100) NULL ,
[图书借阅次数] [int] NULL ,
[期刊借阅次数] [int] NULL ,
[是否挂失] [bit] NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[图书罚款] (
[罚款编号] [int] IDENTITY (1, 1) NOT NULL ,
[图书编号] [varchar] (50) NULL ,
[读者编号] [varchar] (20) NULL ,
[罚款日期] [smalldatetime] NULL ,
[应罚金额] [money] NULL ,
[实收金额] [money] NULL ,
[是否交款] [bit] NULL ,
[备注] [varchar] (100) NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[图书归还] (
[归还编号] [int] IDENTITY (1, 1) NOT NULL ,
[图书编号] [varchar] (50) NULL ,
[读者编号] [varchar] (40) NULL ,
[退还押金] [money] NULL ,
[归还时间] [smalldatetime] NULL ,
[操作员] [varchar] (20) NULL ,
[确定归还] [bit] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[图书借阅] (
[借阅编号] [int] IDENTITY (1, 1) NOT NULL ,
[图书编号] [varchar] (50) NULL ,
[读者编号] [varchar] (40) NULL ,
[借阅时间] [smalldatetime] NULL ,
[应还时间] [smalldatetime] NULL ,
[续借次数] [int] NULL ,
[操作员] [varchar] (20) NULL ,
[状态] [varchar] (10) NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[读者类型] (
[类型] [varchar] (20) NOT NULL ,
[图书册书] [smallint] NULL ,
[期刊册书] [smallint] NULL ,
[续借次数] [int] NULL ,
[限制图书] [bit] NULL ,
[限制期刊] [bit] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[图书征订] (
[征订编号] [varchar] (50) NOT NULL ,
[图书编号] [varchar] (50) NOT NULL ,
[订购者] [varchar] (100) NULL ,
[订购者联系方式] [varchar] (100) NULL ,
[订购数量] [int] NULL ,
[订购单价] [money] NULL ,
[订购日期] [smalldatetime] NULL ,
[是否验收] [bit] NULL ,
[验收日期] [smalldatetime] NULL ,
[操作员] [varchar] (20) NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[图书信息] (
[条形码] [varchar] (40) NULL ,
[编号] [varchar] (50) NOT NULL ,
[书名] [varchar] (200) NULL ,
[类型] [varchar] (100) NULL ,
[作者] [varchar] (100) NULL ,
[译者] [varchar] (100) NULL ,
[ISBN] [varchar] (40) NULL ,
[出版社] [varchar] (100) NULL ,
[价格] [money] NULL ,
[页码] [int] NULL ,
[书架名称] [varchar] (200) NULL ,
[现存量] [int] NULL ,
[库存总量] [int] NULL ,
[入库时间] [smalldatetime] NULL ,
[操作员] [varchar] (20) NULL ,
[简介] [varchar] (100) NULL ,
[借出次数] [int] NULL ,
[是否注销] [bit] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[图书类型] (
[类型编号] [varchar] (50) NOT NULL ,
[类型名称] [varchar] (100) NULL ,
[可借天数] [int] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[图书信息] WITH NOCHECK ADD
CONSTRAINT [PK_图书信息] PRIMARY KEY CLUSTERED
(
[编号]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[图书借阅] WITH NOCHECK ADD
CONSTRAINT [PK_图书借阅] PRIMARY KEY CLUSTERED
(
[借阅编号]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[图书归还] WITH NOCHECK ADD
CONSTRAINT [PK_图书归还] PRIMARY KEY CLUSTERED
(
[归还编号]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[图书罚款] WITH NOCHECK ADD
CONSTRAINT [PK_图书罚款] PRIMARY KEY CLUSTERED
(
[罚款编号]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[图书借阅] ADD
CONSTRAINT [图书借阅_图书编号_fk] FOREIGN KEY
(
[图书编号]
) REFERENCES [dbo].[图书信息] (
[编号]
)
GO
ALTER TABLE [dbo].[图书归还] ADD
CONSTRAINT [图书归还_图书编号_fk] FOREIGN KEY
(
[图书编号]
) REFERENCES [dbo].[图书信息] (
[编号]
)
GO
ALTER TABLE [dbo].[图书罚款] ADD
CONSTRAINT [FK_图书罚款_读者信息] FOREIGN KEY
(
[读者编号]
) REFERENCES [dbo].[读者信息] (
[编号]
),
CONSTRAINT [FK_图书罚款_图书信息] FOREIGN KEY
(
[图书编号]
) REFERENCES [dbo].[图书信息] (
[编号]
)
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
create proc sf_图书借阅
as
begin tran
--借书出库,减少图书库存量
update 图书信息 set 现存量 = isnull(现存量,0) - 1
from 图书信息 as a, 图书借阅 as b
where a.编号=b.图书编号 and b.状态='新借'
--设置借阅状态
update 图书借阅 set 续借次数=0,状态='未还'
where 状态='新借'
commit
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
create proc sf_图书归还 @借阅编号 int, @罚款金额 money
as
begin tran
--借书出库,减少图书库存量
update 图书信息 set 现存量 = isnull(现存量,0) + 1
from 图书信息 as a, 图书借阅 as b
where a.编号=b.图书编号 and b.借阅编号=@借阅编号
-- 如果罚金不是0, 在 图书罚款 中产生记录
if @罚款金额<> 0
insert into 图书罚款(图书编号,读者编号,罚款日期,应罚金额,是否交款)
select 图书编号,读者编号,getdate(), @罚款金额, 0
from 图书借阅 where 借阅编号=@借阅编号
-- 插入图书归还表
insert into 图书归还(图书编号,读者编号,归还时间)
select 图书编号,读者编号,getdate()
from 图书借阅 where 借阅编号=@借阅编号
--设置借阅状态
update 图书借阅 set 状态='已还' where 借阅编号=@借阅编号
commit
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
create proc sf_图书征订
as
begin tran
--验收入库,增加图书库存量
update 图书信息 set 库存总量 = isnull(库存总量,0) + b.订购数量,
现存量 = isnull(现存量,0) + b.订购数量, 入库时间 = b.验收日期
from 图书信息 as a, 图书征订 as b
where a.编号=b.图书编号 and b.是否验收=0
--设置图书征订记录的标志,标志为已验收
update 图书征订 set 验收日期=getdate(), 是否验收=1
where 是否验收=0
commit
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
图书管理系统.zip （41个子文件） 
图书管理系统 第三章 
_desktop.ini 9B 代码 frmReaderDoc.resx 29KB frmMoney.resx 15KB frmBuyBook.cs 22KB frmReaderType.cs 19KB DataSet1.cs 120KB bin Debug 
图书馆管理系统.exe 336KB _desktop.ini 9B 图书馆管理系统.pdb 318KB _desktop.ini 9B DataSet1.xsd 7KB frmBookType.resx 17KB frmBuyBook.resx 26KB 图书馆管理系统.suo 11KB frmList.cs 4KB frmMoney.cs 18KB frmBookDoc.resx 28KB 图书馆管理系统.csproj.user 2KB frmReturn.cs 7KB frmMain.cs 8KB frmBase.cs 14KB frmReaderType.resx 19KB frmSelectBook.cs 26KB _desktop.ini 9B frmReaderDoc.cs 40KB 图书馆管理系统.csproj 10KB frmBase.resx 23KB frmReturn.resx 9KB App.ico 1KB frmBorrow.cs 18KB frmSelectBook.resx 12KB frmBorrow.resx 23KB DataBase.cs 1KB frmBookDoc.cs 41KB 图书馆管理系统.sln 913B frmMain.resx 12KB frmBookType.cs 13KB frmList.resx 7KB AssemblyInfo.cs 2KB 数据库脚本 _desktop.ini 9B Chapter3.sql 6KB资源评论
