数据库课程设计----音响店VCD零售出租管理系统的设计

---数据库创建 USE [master] GO CREATE DATABASE [VCD_info] ON PRIMARY ( NAME = N'VCD_info', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\VCD_info.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ) LOG ON ( NAME = N'VCD_info_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\VCD_info_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%) GO ---VCD信息表 create table VCD ( Vno char(10)not null primary key, Vname char(10) not null, Actor char(10) null, Price decimal(10,2) not null, Vtype int not null, amount int not null check(amount>=0) ) create table Client ( Cno char(10)not null primary key, Cnum char(11) null, Ctel char(11) null, ) ---客户信息表 create table Hire ( Vno char(10) not null , Cno char(10)not null , Hprice decimal null, Hamount int null, Htime char(10) null, Rtime char(10) null, Vstate char(10)not null, primary key(Vno,Cno), foreign key (Vno)references VCD(Vno), foreign key (Cno)references Client(Cno) ) ---零售信息表 create table Sell ( Vno char(10)not null, Stime char(10) not null, Samount int not null, primary key(Vno,Samount), foreign key (Vno)references VCD(Vno) ) ---入库信息表 create table Stock ( Vno char(10)not null, In_time char(10)not null, In_amount int not null, primary key(Vno,In_amount), foreign key (Vno)references VCD(Vno) ) ---客户编号索引 create index user_id on Client(Cno) ---对VCD编号建索引 create index vcd_id on VCD(Vno) ---对VCD名称建索引 Create index vcd_index On VCD(Vname) ---客户信息视图 go create view 用户 as select * from Client go ---查询各类VCD的库存情况 go Create view V_type_number(A_ID,A_amount) As Select VCD.Vtype,sum(VCD.amount) From VCD Group by VCD.Vtype go ---用户查询VCD信息视图 go create view User_Query as select * from VCD go ---管理员查询用户借阅信息视图 go create view Manager_User(VCD编号,归还时间,归还状态,客户姓名,客户电话) as select Hire.Vno,Hire.Rtime,Hire.Vstate,Client.Cnum,Client.Ctel from Hire, Client where Hire.Cno=Client.Cno go ---出租VCD时更新VCD数量信息 create trigger _Hire1 on Hire for insert as update VCD set VCD.amount =VCD.amount-inserted.Hamount from VCD,inserted where VCD.Vno=inserted.Vno ---归还时自动修改VCD现货数 go create trigger _Hire2 on Hire for insert as update VCD set VCD.amount=VCD.amount+inserted.Hamount from VCD,inserted where VCD.Vno=inserted.Vno go ---入库时自动更改库存 create trigger _Stock on Stock for insert as update VCD set VCD.amount =VCD.amount+inserted.In_amount from VCD,inserted where VCD.Vno=inserted.Vno ---售出时自动更改现货量 go create trigger _Sell on Sell for insert as update VCD set VCD.amount=VCD.amount-inserted.Samount from VCD,inserted where VCD.Vno=inserted.Vno go ---VCD编号不可修改其余参数可修改 Create Procedure Pro_VCD_change @Vno_in char(10), @Vname_out char(10), @Actor_out char(10), @Price_out decimal(10,2), @Vtype_out char(10), @Amount_out int As Begin Update VCD set Vname=@Vname_out, Actor=@Actor_out, Price=@Price_out, Vtype=@Vtype_out, amount=@Amount_out Where Vno=@Vno_in End Go ---给所有VCD打九折 Create procedure Proc_TotalPrice As Begin Update VCD set Price=Price*0.9 From VCD End ---各类VCD的库存查询依据是使用名字查询 go Create function selectscene( ) Returns int As Begin Declare @Vtype int,@amount_out int,@Vname char(10) Select @Vtype=Vtype From VCD Where Vname=@Vname Select @Amount_out=sum(VCD.amount) From VCD Where Vtype=@Vtype Return @Amount_out End Go ---设置借出归还销售的数量，将借出，归还，销售分别用0，1，2表示 Create function timescene(@begintime datetime,@endtime datetime,@flage int) Returns int As Begin Declare @countnumber int set @countnumber=0 if @flage=0 begin select @countnumber=@countnumber+Hamount from Hire where Htime<=@endtime and Htime>=@begintime end else if @flage=1 begin select @countnumber=@countnumber+Hamount from Hire where Rtime<=@endtime and Rtime>=@begintime end else begin select @countnumber=@countnumber+Samount from Sell where Stime<=@endtime and Stime>=@begintime end return @countnumber end