一个简单图书管理系统TSGL的应用数据库

简单图书管理系统的应用数据库

一.摘要

这个项目是学校结课要求的项目，所以采用的是SQL sever2008来完成的，并不是mysql来完成的，SQL sever2008与mysql就基础知识没有什么大的区别，两者的DDL,DQL,DML,DCL语言大致相同。

二.需求分析

三.具体实现

/*
建表
(1)对表1、表2、表3分别设置主外键
(2)对表1中的sex列设置默认值为“男”。
提示:sex  CHAR(2)  check(sex in('男','女')) default '男'
(3)要求表3中的borrowtime列取值范围在2010-01-01日到2010-12-31日之间
提示:borrowtime  date check(borrowtime  between '2010-01-01'  and  '2010-12-31')
*/
CREATE TABLE reader          
      (readerno  varchar(10) PRIMARY KEY,
       readername varchar(10),
       sex  char(2),
       tel  varchar(11)
      ); 

CREATE TABLE book          
      (bookno  varchar(12) PRIMARY KEY,
       bookname varchar(20),
       publisher varchar(20),
       author  varchar(10),
       price numeric(7,4)
      ); 

CREATE TABLE borrow          
      (bookno  varchar(12),
       borrowtime datetime,
       returntime datetime,
       readerno  varchar(10),
       isreturn char(2),
       PRIMARY KEY (bookno,readerno),
       FOREIGN KEY (bookno) REFERENCES book(bookno),
       FOREIGN KEY (readerno)REFERENCES reader(readerno)
      ); 
   
      /*插入记录*/
insert into reader values('302009301','张小兰','女','13511112222');
insert into reader values('302009302','李光铎','男','13522223333');
insert into reader values('302009303','刘杰','男','13533334444');
insert into reader values('302009304','柯勇','男','13644445555');
insert into reader values('302009305','刘方','男','13655556666');
insert into reader values('302009306','张思','男','13966667777');
insert into reader values('302009308','陈丽红','女','13400002233');
insert into reader values('302009310','胡兴','女','13211112222');

insert into book values('102009123101','数据库开发','冶金工业出版社','余芳',38);
insert into book values('102009123102','ASP.NET','清华大学出版社','郑齐心',65);
insert into book values('102009123103','网页设计与制作','铁道工业出版社','刘云峰',35);
insert into book values('102009123104','C语言程序设计','清华大学出版社','谭浩强',31);
insert into book values('102009123105','JAVA程序设计','治金工业出版社','刘华',34.5);
insert into book values('102009123106','C#程序设计','清华大学出版社','张文军',32.8);
insert into book values('102009123107','计算机网络','铁道工业出版社','李拓',32);
insert into book values('102009123109','数据库系统概论','高等教育出版社','王珊',39.6);

insert into borrow values('102009123101','2010-05-01','2010-6-10','302009301','否');
insert into borrow values('102009123103','2010-03-06','2010-4-6','302009306','否');
insert into borrow values('102009123105','2010-04-02','2010-5-25','302009301','否');
insert into borrow values('102009123101','2010-07-03','2010-8-3','302009302','否');
insert into borrow values('102009123105','2010-09-01','2010-10-1','302009303','否');
insert into borrow values('102009123106','2010-10-01','2010-11-1','302009301','否');
insert into borrow values('102009123101','2010-01-02','2010-2-8','302009303','否');

select * from reader
select * from book
select * from borrow

/*3.用T-SQL实现TSGL数据库的完整性功能*/
alter table reader add constraint p1 primary key(readerno)
alter table book add constraint p2 primary key(bookno)
alter table borrow add constraint p3 primary key(bookno,readerno)
alter table borrow add constraint f1 foreign key(bookno) references book(bookno)
alter table borrow add constraint f2 foreign key(readerno) references reader(readerno)


create default d1 as '男' 
Sp_bindefault 'd1','reader.sex'
/*或在定义表reader时:sex  CHAR(2)  check(sex in('男','女')) default '男'*/

alter table borrow
Add constraint c1 check(borrowtime between '2010-01-01' and '2010-12-31')
/*或在定义表borrow时:borrowtime  date check(borrowtime  between '2010-01-01'  and  '2010-12-31')*/


/*4.用T-SQL完成如下问题功能*/
/*(1)查询“柯勇”读者的读者号,姓名和电话*/
select readerno,readername,tel from reader where readername='柯勇'
/*(2)查询统计读者号为”302009301”号的读者借阅书本的数目*/
select count(*) from borrow where readerno='302009301'
/*(3)创建一个视图tuv1，要求显示读者的读者号，借阅的图书名和图书价格*/
create view tuv1 as  
select readerno,bookname,price from book,borrow
where book.bookno=borrow.bookno

select * from tuv1
 /*(4)创建一个存储过程tupro，要求用户指定一个图书号，就能显示该图书被借的次数。*/
create proc tupro(@bookno varchar(12))
    As
    Select count(*) from borrow where bookno=@bookno

tupro '102009123101'
tupro '102009123105'
tupro '102009123103'
 /*(5)为数据表表3创建一个INSERT和UPDATE触发器。当试图插入和修改数据表3中的记录时，检查修改后记录中的rederno（读者号）是否在数据表1存在，同时bookno是否在数据表2中存在，若不是同时存在，则撤消INSERT或UPDATE操作，并返回一条错误消息。。*/
create trigger aa
  On borrow
  For insert,update
  As
  Declare @rederno varchar(10),@bookno varchar(12)
  Begin transaction
  Set @rederno=(select readerno from inserted)
  Set @bookno=(select bookno from inserted) 
  If @rederno not in(select readerno from reader) 
    Begin
      Rollback transaction
      Print '修改或插入失败!'
    end
  Else
    Begin
      Commit transaction
      Print '修改或插入成功!'
    End

/*测试触发器*/
update borrow set readerno='302009316' where readerno='302009306'
update borrow set readerno='302009302' where readerno='302009306'
insert into borrow values('102009123107','2010-4-2','2010-4-9','302009305','否');
insert into borrow values('102009123107','2010-4-2','2010-4-9','302009309','否');

/*(6)在beifen磁盘设备上对TSGL数据库作全库备份后，现在对TSGL数据库做删除操作，然后再从备份文件中恢复。*/
Exec sp_addumpdevice 'disk','mybackup','D:\bak\tsglbak.bak'
Backup database TSGL to mybackup
Drop database TSGL
Use master
Restore database TSGL from mybackup with file=1