sql

数据库设计

Use master
go

if (select count(*) from sysdatabases where name = 'Book') > 0 
   begin
	Drop Database Book
   end
go

--创建进货数据库

Create DataBase Book
go

Use Book
go

--Drop DataBase Book

--创建表
--部门表
Create Table 部门
(
   部门号 int primary key identity(10001,1),
   部门名 varchar(20) unique not null,
   部门电话号 varchar(13) unique not null
)

Insert 部门 values('管理部门','027-87853412')
Insert 部门 values('财政部门','027-87853413') 


--采购员表
Create Table 采购员
(
   采购员号	int primary key identity(101,1),
   采购员名	varchar(10) not null,
   采购员性别	char(2) not null check(采购员性别 = '男' or 采购员性别 = '女'),
   采购员年龄	int not null,
   采购员电话	varchar(13) not null
)

Insert 采购员 values('江舟','男',24,'13542698411')
Insert 采购员 values('刘瑜','男',23,'13018736547')


--管理员表
Create Table 管理员
(
   管理员号	int primary key Identity(1001,1),
   管理员名	char(10) unique,
   管理员性别	char(2) not null check(管理员性别 = '男' or 管理员性别 = '女'),
   管理员年龄	int not null,
   管理员电话	varchar(13) not null
)

Insert 管理员 values('喻果强','男',24,'13542698461')
Insert 管理员 values('张饮','男',24,'13018736457')



--供应商表
Create Table 供应商
(
   供应商号	int primary key Identity(1001,1),
   供应商名	varchar(20) not null,
   供应商地址	varchar(40),
   供应商电话	varchar(13) not null
)

Insert 供应商 values('武汉图书城','雄楚大道','027-87412365')
Insert 供应商 values('光谷图书城','鲁磨路','027-85712946')


--图书分类表
Create Table 图书分类
(
   分类号	int primary key Identity(1001,1),
   分类名	varchar(20) unique not null
)

Insert 图书分类 values('计算机语言类')
Insert 图书分类 values('英语类')


--图书库表
Create Table 图书库
(
   图书号	int primary key Identity(10000001,1),
   图书名	varchar(40) unique not null,
   作者		varchar(10) not null,
   价格		money not null,
   出版社	varchar(30) not null,
   库存量	int not null,
   分类号	int foreign key references 图书分类(分类号)
)

Insert 图书库 values('数据结构(用面向对象方法与C++描述)','殷人昆等',26.00,'清华大学出版社',10,1001)
Insert 图书库 values('C++语言程序设计','郑莉等',23.00,'清华大学出版社',5,1001)
Insert 图书库 values('新概念英语2','亚历山大等',29.90,'外语教学与研究出版社',25,1002)


--管理员分类表
Create Table 管理员分类
(
   管理员号 	int foreign key references 管理员(管理员号),
   分类号	int foreign key references 图书分类(分类号)		
)
Insert 管理员分类 values(1001,1002)
Insert 管理员分类 values(1001,1001)
Insert 管理员分类 values(1002,1001)


--购书计划单表
Create Table 购书计划单
(
   购书计划单号	int primary key Identity(100001,1),
   管理部门购书日期  varchar(10) not null,
   财政部门接收日期  varchar(10),
   采购员号	int foreign key references 采购员(采购员号),
   供应商号	int foreign key references 供应商(供应商号),
   管理员号	int foreign key references 管理员(管理员号)
)

Insert 购书计划单 values('2004-1-15','2004-3-15',101,1001,1001)
Insert 购书计划单 values('2005-3-20','2005-5-10',101,1002,1001)


--部门购书单表
Create Table 部门购书单
(
   部门号	int foreign key references 部门(部门号),
   购书计划单号	int foreign key references 购书计划单(购书计划单号)
)

Insert 部门购书单 values(10001,100001)
Insert 部门购书单 values(10001,100002)
Insert 部门购书单 values(10002,100001)
Insert 部门购书单 values(10002,100002)



--购书图书表
Create Table 购书图书
(
   图书名	varchar(40) primary key,
   出版社	varchar(20) not null,
   图书价格	money not null,
   库存量	smallint not null,
   需求量	smallint not null,
   购书计划单号	int foreign key references 购书计划单(购书计划单号)
)

Insert 购书图书 values('数据结构(用面向对象方法与C++描述)','清华大学出版社',26.00,10,20,100001)
Insert 购书图书 values('C++语言程序设计','清华大学出版社',23.00,5,35,100001)
Insert 购书图书 values('SQL Server 2000(中文版)','清华大学出版社',39.50,0,30,100002)


--提货通知单表
Create Table 提货通知单
(
   提货通知单号	int primary key Identity(10001,1),
   提货单日期	varchar(10),
   接收部门号	int foreign key references 部门(部门号),
   供应商号	int foreign key references 供应商(供应商号),
   采购员号	int foreign key references 采购员(采购员号),
   管理员号	int foreign key references 管理员(管理员号)
)

Insert 提货通知单 values('2004-3-01',10002,1001,101,1001)
Insert 提货通知单 values('2005-4-29',10002,1002,101,1001)


--提货图书表
Create Table 提货图书
(
   图书名	char(40) primary key,
   作者		varchar(10) not null,
   价格		money not null,
   出版社	varchar(30) not null,
   图书数量	int not null,
   提货通知单号  int foreign key references 提货通知单(提货通知单号)
)

Insert 提货图书 values('数据结构(用面向对象方法与C++描述)','殷人昆等',26.00,'清华大学出版社',20,10001)
Insert 提货图书 values('C++语言程序设计','郑莉等',23.00,'清华大学出版社',25,10001)
Insert 提货图书 values('SQL Server 2000(中文版)','周绪等',39.50,'清华大学出版社',20,10002)


--重新提货通知单表
Create Table 重新提货通知单
(
   采购员号	int foreign key references 采购员(采购员号),
   供应商号	int foreign key references 供应商(供应商号),
   不合格书名	varchar(40) unique not null,
   数量		int not null,
   重新提货日期	varchar(10) not null,
   备注		varchar(50),
   primary key(采购员号,供应商号)
)

Insert 重新提货通知单 values(101,1002,'SQL Server 2000(中文版)',10,'2005-3-20','这些书破旧')
Insert 重新提货通知单 values(101,1001,'C++语言程序设计',10,'2005-5-13','这些书破旧,版本号不对')


--入库单表
Create Table 入库单
(
   入库单号	int primary key Identity(10001,1),
   入库日期	varchar(10),
   采购员号	int foreign key references 采购员(采购员号)
)

Insert 入库单 values('2005-3-12',101)
Insert 入库单 values('2005-5-6',101)


--入库图书表
Create Table 入库图书
(
   合格书名	char(40) primary key,
   合格书数量	int not null,
   入库单号	int foreign key references 入库单(入库单号)
)

Insert 入库图书 values('数据结构(用面向对象方法与C++描述)',25,10001)
Insert 入库图书 values('C++语言程序设计',25,10001)
Insert 入库图书 values('SQL Server 2000(中文版)',20,10002)


--采购员发送入库单表
Create Table 采购员发送入库单
(
   管理员号	int foreign key references 管理员(管理员号),
   入库单号	int foreign key references 入库单(入库单号)
)

Insert 采购员发送入库单 values(1001,10001)
Insert 采购员发送入库单 values(1001,10002)













--创建索引
Create nonclustered Index 部门购书单_索引 ON 部门购书单(部门号)
go
--Drop  Index 部门购书单.部门购书单_索引

Create clustered Index 采购员发送入库单_索引 ON 采购员发送入库单(管理员号)
go

Create nonclustered Index 购书图书_索引 ON 购书图书(图书名)
go

Create clustered Index 管理员分类_索引 ON 管理员分类(管理员号)
go

Create nonclustered Index 入库图书_索引 ON 入库图书(合格书名)
go

Create nonclustered Index 图书库_索引 ON 图书库(分类号)
go





--创建视图
Create View SumNum
as
Select 合格书名,合格书数量 + 库存量 as 图书库数量 From 图书库 inner join 入库图书
ON 合格书名 = 图书名
go

--Select * From SumNum


Create View Price
as
Select 提货通知单号,图书名,图书数量*价格 as 总价格 From 提货图书
go

--Select * From Price


Create View SumPrice
as
Select sum(图书数量*价格) as 图书总价 From 提货图书
go

--Select * From SumPrice


--创建存储过程
Create proc ShowPrice
@Name varchar(40)
as
Begin
    Select 提货图书.图书名,作者,出版社,价格,合格书数量,总价格 as 图书价格
    From  入库图书 inner join 提货图书 ON 提货图书.图书名 = 入库图书.合格书名
	  inner join Price ON 提货图书.图书名 = Price.图书名
    Where 提货图书.图书名 = @Name
End

go

--exec ShowPrice 'C++语言程序设计'



--创建触发器
Create Trigger 图书租借					--判断图书是否可租借
on 图书库
for update
as
   if (select 库存量 from inserted) < 0
     begin
	print '对不起，图书以借完！'
	rollback transaction
     end
   else
     begin
	print '还有图书可以租借'
     end
go

--update 图书库
--set 库存量 = 库存量 - 1
--where 图书名 = 'C++语言程序设计' 




--对新进入的图书进行添加
Create proc 图书计算
@BookN varchar(40),			--插入的图书名
@Style int				--插入的图书类型
as
Begin
    If @BookN not IN (Select 图书名 From 图书库)	--判断插入的图书名是否在图书库中
	Insert into 图书库( 图书名,作者,价格,出版社,库存量,分类号)
	Select 图书名,作者,价格,出版社,图书数量,@Style From Book..提货图书
	Where @BookN = 图书名
    Else
	Update 图书库			--如果,图书库中有图书,那么,就只改变图书库中图书的数量
	Set 库存量=库存量+(Select 合格书数量 From 入库图书 Where 合格书名 = @BookN)
	Where 图书名 = @BookN
End
go

declare curBook cursor			--以书名创建一个游标
Scroll					--定义认证标志类型
For
   Select 合格书名 From 入库图书
go
Open curBook				--打开游标
go

declare @Num smallint, @BookName varchar(40), @Styple_id int
Set @Num = 0				--判断入库图书的个数

Fetch first From curBook into @BookName	--取出当前图书名

While @Num < (Select count(*) From 入库图书)	
Begin
    exec 图书计算 @BookName,'1001'		--图书计算
    Fetch next From curBook into @BookName	--取出下一个图书名    
    Set @Num = @Num + 1
End
go

--Select 图书名,作者,价格,出版社,库存量 From 图书库

--Close curBook
--Deallocate curBook


--sp_helptext 图书计算