簡單圖書管理系統的應用數據庫

一.摘要

這個項目是學校結課要求的項目，所以采用的是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.booknoselect * from tuv1/*(4)創建一個存儲過程tupro，要求用戶指定一個圖書號，就能顯示該圖書被借的次數。*/ create proc tupro(@bookno varchar(12))AsSelect count(*) from borrow where bookno=@booknotupro '102009123101' tupro '102009123105' tupro '102009123103'/*(5)為數據表表3創建一個INSERT和UPDATE觸發器。當試圖插入和修改數據表3中的記錄時，檢查修改后記錄中的rederno（讀者號）是否在數據表1存在，同時bookno是否在數據表2中存在，若不是同時存在，則撤消INSERT或UPDATE操作，并返回一條錯誤消息。。*/ create trigger aaOn borrowFor insert,updateAsDeclare @rederno varchar(10),@bookno varchar(12)Begin transactionSet @rederno=(select readerno from inserted)Set @bookno=(select bookno from inserted) If @rederno not in(select readerno from reader) BeginRollback transactionPrint '修改或插入失敗!'endElseBeginCommit transactionPrint '修改或插入成功!'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

總結

以上是生活随笔為你收集整理的一个简单图书管理系统TSGL的应用数据库的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。