chapter3.sql

采用C#和SQL Server 2000开发 功能比较全面的图书馆管理系统

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