數(shù)據(jù)庫(kù)詳細(xì)設(shè)計(jì)說明書

1、精選優(yōu)質(zhì)文檔-傾情為你奉上1.1 目的本文為圖書館管理課程設(shè)計(jì)SQL Server功能規(guī)范說明書。本說明書將：l 描述數(shù)據(jù)庫(kù)設(shè)計(jì)的目的l 說明數(shù)據(jù)庫(kù)設(shè)計(jì)中的主要組成部分l 說明數(shù)據(jù)庫(kù)設(shè)計(jì)中各功能的實(shí)現(xiàn)1.2 內(nèi)容本文檔主要內(nèi)容包括對(duì)數(shù)據(jù)庫(kù)設(shè)計(jì)結(jié)構(gòu)的總體描述，對(duì)數(shù)據(jù)庫(kù)中各種對(duì)象的描述（包括對(duì)象的名稱、對(duì)象的屬性、對(duì)象和其他對(duì)象直接的關(guān)系）。本文檔中包含對(duì)以下數(shù)據(jù)庫(kù)內(nèi)容的描述：l 數(shù)據(jù)表l 視圖l 存儲(chǔ)過程l 觸發(fā)器l 約束在數(shù)據(jù)庫(kù)主要對(duì)象之外，本文還將描述數(shù)據(jù)庫(kù)安全性設(shè)置、數(shù)據(jù)庫(kù)屬性設(shè)置和數(shù)據(jù)庫(kù)備份策略，為數(shù)據(jù)庫(kù)管理員維護(hù)數(shù)據(jù)庫(kù)安全穩(wěn)定地運(yùn)行提供參考。1.3 與其他項(xiàng)目的關(guān)聯(lián)本項(xiàng)目的數(shù)據(jù)庫(kù)設(shè)計(jì)與

2、本項(xiàng)目（Web部分和Windows部分）功能密切相關(guān)。本案例項(xiàng)目的數(shù)據(jù)庫(kù)將按照項(xiàng)目程序部分的功能需求而設(shè)計(jì)，數(shù)據(jù)庫(kù)設(shè)計(jì)將配合設(shè)計(jì)案例的程序部分，以實(shí)現(xiàn)一個(gè)功能完備的真實(shí)環(huán)境內(nèi)的應(yīng)用。表1.4 表設(shè)計(jì)概述根據(jù)設(shè)計(jì)的系統(tǒng)功能，數(shù)據(jù)庫(kù)將以圖書信息為中心存儲(chǔ)相關(guān)數(shù)據(jù)，配合 SQL Server 數(shù)據(jù)庫(kù)系統(tǒng)中提供的數(shù)據(jù)管理，實(shí)現(xiàn)圖書的借閱、歸還、續(xù)借及系統(tǒng)設(shè)置等業(yè)務(wù)功能。數(shù)據(jù)庫(kù)設(shè)計(jì)將以存儲(chǔ)讀者信息的讀者表為基礎(chǔ)，連接多張相關(guān)表以實(shí)現(xiàn)對(duì)以下關(guān)系的支持：l 顧客瀏覽記錄l 顧客訂購(gòu)記錄l 顧客退訂記錄l 顧客付款記錄l 顧客對(duì)花卉的評(píng)價(jià)l 顧客對(duì)花卉的建議及留言。圖 011.5 顧客用戶信息表（Reader

3、Info）概述顧客用戶信息表用于記錄顧客基本信息，并作為基礎(chǔ)表與其他表連接。表定義讀者用戶信息表定義如Error! Reference source not found.所示。表 01字段類型可否為空備注Usernamenvarchar(10)否IDint否Passwdchar(8)否Namenvarchar(10)可Sexchar(2)否Ageint可StuNumNumeric可PlaceStrandnvarchar(50)可PlaceClassnvarchar可ValidCardnvarchar(50)否BookCardnvarchar(10)可CreateTimedatetime可Exp

4、iredTimedatetime可主鍵用戶信息表的主鍵是用戶信息表編號(hào)字段，類型為整型，設(shè)置自動(dòng)增量。約束無索引主鍵用戶信息ID具有自動(dòng)創(chuàng)建的聚集索引。1.6 管理員用戶信息表（UserInfo）概述管理員用戶信息表用于記錄系統(tǒng)管理員的各項(xiàng)資料。表中都記錄了管理員的賬號(hào)和密碼，是各用戶的詳細(xì)記錄。表定義管理員用戶信息表定義如Error! Reference source not found.所示。表 02字段類型可否為空備注IDNumeric不否Usernamenvarchar(10)不否Passwdchar(8)不否Typenvarchar(14)不否主鍵管理員用戶信息表的主鍵是管理員用戶I

5、D號(hào)，類型為整型，設(shè)置自動(dòng)增量。約束無其他約束。索引主鍵字段管理員編號(hào)具有自動(dòng)創(chuàng)建的聚集索引。用戶類別表（ReaderType）讀者類別表用于記錄顧客信息，包括名字、地址等。表定義讀者類別表定義如Error! Reference source not found.所示。表 03字段類型可否為空備注IDint否Namenvarchar(10)否ReaderIDint 否Canborrowint 可主鍵讀者類別表的主鍵是讀者表ID字段，類型為整型，設(shè)置自動(dòng)增量。約束無其他約束。索引主鍵字段薪資編號(hào)具有自動(dòng)創(chuàng)建的聚集索引。花束信息表(BookInfo)圖書信息表用于記錄花束信息（花束名稱、花束等），

6、通過字段花束類別號(hào)與花束類別表關(guān)聯(lián)。表定義圖書信息表定義如Error! Reference source not found.所示。表 04名稱類型可否為空備注IDnvarchar(20) 否Namenvarchar(50)否Typeidint 可Statebinary 可Creattimedatetime否Pricemoney可Pressnvarchar(50) 否IsCancellationbinary可IDCancellationint可Notenvarchar(50)可Authornvarchar(10)可Pageint可主鍵花束信息表的主鍵是花束編號(hào)字段，類型為整型。約束花束信息表中

7、的圖書編號(hào)不可相同；花束信息表中的狀態(tài)類型字段只能取“被借”、“續(xù)借”、“以還”。索引主鍵字段考勤編號(hào)具有自動(dòng)創(chuàng)建的聚集索引?；ㄊ悇e表（BookType) 花束類別表用于記錄圖書的類別。圖書類別表中的基本信息包括：花束的類別、花束的花語，花束的價(jià)格。表定義圖書類別表定義如Error! Reference source not found.所示。表 05字段類型可否為空備注Typeidint否Typenamenvarchar(50)否主鍵花束類別表的主鍵是Typeid字段，類型為整型，設(shè)置自動(dòng)增量。約束訂購(gòu)開始時(shí)間應(yīng)小于付款結(jié)束時(shí)間；索引主鍵字段請(qǐng)假申請(qǐng)編號(hào)具有自動(dòng)創(chuàng)建的聚集索引。1.7 花卉

8、庫(kù)存表(BookStock)概述花卉庫(kù)存表用于花卉總庫(kù)存的記錄。庫(kù)存表中的基本信息包括：花卉ID。通過字段，圖書ID與花卉信息表關(guān)聯(lián)。表定義圖書庫(kù)存表定義如Error! Reference source not found.所示。表 06字段類型可否為空備注IDint否BookIDnvarchar(20)否索引主鍵字段加班申請(qǐng)編號(hào)具有自動(dòng)創(chuàng)建的聚集索引。表定義借書表定義如Error! Reference source not found.所示。表 07字段類型可否為空備注IDNumeric 否BookIDnvarchar(20)否CardIDNumeric 可BorrowTimedatetim

9、e 否ReturnTimedatetime 否IsReturnnvarchar(10)否索引主鍵字段業(yè)績(jī)?cè)u(píng)定編號(hào)具有自動(dòng)創(chuàng)建的聚集索引。1.8 評(píng)價(jià)表(Evaluate)概述評(píng)價(jià)表用于保存顧客對(duì)花卉看法的記錄。評(píng)價(jià)表中的基本信息包括：評(píng)價(jià)ID、花卉ID、評(píng)價(jià)等。表定義評(píng)價(jià)表定義如Error! Reference source not found.所示。表 08字段類型可否為空備注IDint否BookIDint否ReaderIDnvarchar(20)否Evaluatenvarchar(50)主鍵評(píng)價(jià)表的主鍵是評(píng)價(jià)編號(hào)字段，類型為整型，設(shè)置自動(dòng)增量。約束無其他約束。索引主鍵字段評(píng)價(jià)編號(hào)具有自動(dòng)創(chuàng)

10、建的聚集索引。1.9 花卉證表(BookCard)概述花卉證表用于記錄圖書證ID信息?；ɑ茏C表的基本信息包括：花卉證表編號(hào)、顧客編號(hào)等。表定義圖書證表定義如Error! Reference source not found.所示。表 09字段類型可否為空備注IDNumeric 否ReaderIDint 否Borrowedint否主鍵圖書證表的主鍵是圖書證ID編號(hào)字段，類型是整型，設(shè)置自動(dòng)增量。約束無。索引主鍵字段圖書證表編號(hào)具有自動(dòng)創(chuàng)建的聚集索引。1.10 留言表(Message)概述留言表用于記錄顧客對(duì)花卉相關(guān)的留言。留言表包含的主要內(nèi)容有：留言表編號(hào)、顧客編號(hào)。表定義留言表定義如Error

11、! Reference source not found.所示。表 010名稱類型可否為空說明備注IDint 否業(yè)績(jī)?cè)u(píng)定子項(xiàng)目編號(hào)ReaderIDnvarchar(20) 否業(yè)績(jī)?cè)u(píng)定編號(hào)detailsnvarchar否項(xiàng)目?jī)?nèi)容主鍵留言表編號(hào)。約束無其他約束。索引主鍵字段留言表編號(hào)具有自動(dòng)創(chuàng)建的聚集索引。1.11 系統(tǒng)設(shè)置表(SystemSet) 概述系統(tǒng)設(shè)置表用于系統(tǒng)各詳細(xì)設(shè)置信息。系統(tǒng)設(shè)置表包含的主要內(nèi)容有：用戶權(quán)限設(shè)置、賠償價(jià)格參照、公告、證件相關(guān)各信息和備份恢復(fù)等。表定義系統(tǒng)設(shè)置表定義如Error! Reference source not found.所示。表 011名稱類型可否為空說

12、明備注IDint 否UserPermissionSetnvarchar(10) 否Overdatepointmoney否lostPricemoney可RenewTimedatetime可BackupSetdatetimeAnnouncenvarchar(50)CardReCostmoneyCardfillpricemoneyCardReTimedatetimeModifyPersonnvarchar(10)Modifytimedatetime主鍵系統(tǒng)設(shè)置表編號(hào)。約束無其他約束。索引主鍵字段系統(tǒng)設(shè)置表編號(hào)具有自動(dòng)創(chuàng)建的聚集索引。1.12 各表SQL代碼示例管理員用戶信息表（UserInfo）cr

13、eate table UserInfo( ID Numeric identity(1001,1) not null, Username nvarchar(10) not null, Passwd char(8) not null, Type nvarchar(14) check(Type =花卉管理員 or Type =系統(tǒng)管理員) not null, constraint pk_UserInfo_id primary key(ID) 用戶用戶信息表（ReaderInfo）（普通用戶）create table ReaderInfo( Username nvarchar(10) not null

14、, ID int not null, Passwd char(8) not null, Name nvarchar(10) not null, Sex char(2) check(Sex =男 or Sex =女)not null, Age int , StuNum Numeric , PlaceStrand nvarchar(50), PlaceClass nvarchar(50), ValidCard nvarchar(50), BookCard nvarchar(10), CreateTime datetime not null, ExpiredTime datetime not nul

15、l, constraint pk_ReaderInfo_id primary key (ID)顧客類別表（ReaderType）Create table ReaderType(ID int ,Name nvarchar(10),ReaderID int not null,Canborrow int,constraint pk_ReaderType_id primary key (ID)alter table ReaderType add constraint fk_ReaderType_ReaderInfo foreign key (ReaderID) references ReaderInf

16、o (ID);*花卉信息表(BookInfo)create table BookInfo( ID nvarchar(20) not null, Name nvarchar(50) , Typeid int, State binary , Creattime datetime , Price money , Press nvarchar(50) , IsCancellation binary , IDCancellation int , Note nvarchar(50) , Author nvarchar(10) , Page int , constraint pk_BookInfo_id p

17、rimary key (ID)alter table BookInfo add constraint fk_BookInfo_BookType foreign key (Typeid) references BookType (Typeid);花卉類別表（BookType)create table BookType( Typeid int , Typename nvarchar(50), constraint pk_BookType_id primary key(Typeid)花卉庫(kù)存表(BookStock)Create table BookStock( ID int , BookID nva

18、rchar(20) not null,constraint pk_BookStock_id primary key (ID) alter table BookStock add constraint fk_BookStock_BookInfo foreign key (BookID) references BookInfo (ID);花卉訂購(gòu)表（BookBorrow)create table BookBorrow( ID Numeric, BookID nvarchar(20), CardID Numeric, BorrowTime datetime, ReturnTime datetime,

19、 IsReturn nvarchar(10),constraint pk_BookBorrow_id primary key (ID)alter table BookBorrow add constraint fk_BookBorrow_BookInfo foreign key (BookID) references BookInfo (ID);花卉訂購(gòu)表LendRoom( ID int , Name nvarchar(10), LendRoomBookID nvarchar(20) ,constraint pk_LendRoom_id primary key (ID)alter table

20、LendRoom add constraint fk_LendRoom_LendRoomBook foreign key (BookID) references BookInfo (BookID);花卉支付籍表LendRoomBookCreate table LendRoomBook( ID nvarchar(20) not null, BookID int not null, BookCount int , BookState binary ,constraint pk_LendRoomBook_id primary key (ID)alter table LendRoomBook add

21、constraint fk_LendRoomBook_BookInfo foreign key (BookID) references BookInfo (ID);用戶驗(yàn)證表BookCardCreate table BookCard( ID Numeric , ReaderID int not null, Borrowed int ,constraint pk_BookCard_id primary key (ID)alter table BookCard add constraint fk_BookCard_ReaderInfo foreign key (ReaderID) referenc

22、es ReaderInfo (ID);留言表Messagecreate table Msssage( ID int , ReaderID nvarchar(20) not null, details nvarchar(50) not null, constraint pk_Msssage_id primary key (ID)alter table Msssage add constraint fk_Msssage_ReaderInfo foreign key (ReaderID) references ReaderInfo (ID);系統(tǒng)設(shè)置表SystemSetCreate table Sy

23、stemSet(ID intUserPermissionSet nvarchar(10)Overdatepoint moneylostPrice moneyRenewTime datetimeBackupSet datetimeAnnounce nvarchar(50)CardReCost moneyCardfillprice moneyCardReTime datetimeModifyPerson nvarchar(10)Modifytime datetime constraint pk_SystemSet_id primary key (ID) 視圖1.13 系統(tǒng)設(shè)置表(SystemSet

24、) 概述l 此視圖通過左外連接員工和部門表得到了員工的詳細(xì)信息，其中包括員工的基本信息、員工的部門信息和員工經(jīng)理信息。l Web用戶具有查看的權(quán)限?；韑 BookInfol LendRoomBook視圖包含字段l dbo.BookInfo.IDl dbo.BookInfo.Namel dbo.LendRoomBook.Timesl dbo.BookInfo.Typeidl dbo.BookInfo.Pricel dbo.BookInfo.Pressl dbo.BookInfo.Notel dbo.BookInfo.Authorl dbo.BookInfo.Statel dbo.BookInf

25、o.Pagel dbo.BookInfo.Creattime代碼示例:SELECT dbo.BookInfo.ID, dbo.BookInfo.Name, dbo.LendRoomBook.Times, dbo.BookInfo.Typeid, dbo.BookInfo.Price, dbo.BookInfo.Press, dbo.BookInfo.Note, dbo.BookInfo.Author, dbo.BookInfo.State, dbo.BookInfo.Page, dbo.BookInfo.CreattimeFROM dbo.BookInfo INNER JOIN dbo.Len

26、dRoomBook ON dbo.BookInfo.ID = dbo.LendRoomBook.BookID存儲(chǔ)過程1.14 數(shù)據(jù)庫(kù)備份概述l 系統(tǒng)備份到D盤USE LMS_xiliGO/* Object: StoredProcedure dbo.backup00 Script Date: 06/17/2011 08:53:13 */SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER proc dbo.backup00as BACKUP DATABASE LMS_xili TO DISK = d:dbBackTest.bak1.15 數(shù)據(jù)庫(kù)還原

27、l 系統(tǒng)從D盤備份中還原數(shù)據(jù)庫(kù)USE LMS_xiliGO/* Object: StoredProcedure dbo.restore00 Script Date: 06/17/2011 08:53:22 */SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER proc dbo.restore00as restore DATABASE LMS_xili from DISK = d:dbBackTest.bak1.16 清空表l 清空bookborrowreturn這張表USE LMS_xiliGO/* Object: StoredProcedu

28、re dbo.sp_clearBookBorrowReturn Script Date: 06/17/2011 08:53:29 */SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER proc dbo.sp_clearBookBorrowReturnastruncate table BookBorrowReturn1.17 清空表l 清空BookInfo這張表USE LMS_xiliGO/* Object: StoredProcedure dbo.sp_clearBookInfo Script Date: 06/17/2011 08:53:3

29、5 */SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER proc dbo.sp_clearBookInfoastruncate table BookInfo1.18 清空表l 清空LendRoomBook這張表USE LMS_xiliGO/* Object: StoredProcedure dbo.sp_clearLend Script Date: 06/17/2011 08:53:44 */SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER proc dbo.sp_clearLendast

30、runcate table LendRoomBook1.19 清空表l 清空ReaderInfo這張表USE LMS_xiliGO/* Object: StoredProcedure dbo.sp_clearReaderInfo Script Date: 06/17/2011 08:53:50 */SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER proc dbo.sp_clearReaderInfoastruncate table ReaderInfo觸發(fā)器1.20 向公告表中插入數(shù)據(jù)則自動(dòng)修改其字段狀態(tài)概述 當(dāng)向announce表中插入數(shù)

31、據(jù)時(shí),修改其另一字段狀態(tài)為1觸發(fā)器影響范圍公告表USE LMS_xiliGO/* Object: Trigger dbo.an_update Script Date: 06/17/2011 08:54:41 */SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER trigger dbo.an_update on dbo.Announcefor insert as declare id intbeginselect id = id from inserted;update announce set pustate = 0 where id = i

32、d - 1 ;end1.21 花卉表中插入數(shù)據(jù)則更新新品花卉的狀態(tài)概述 當(dāng)向借書表中插入數(shù)據(jù)時(shí),則表示書本被借,這時(shí)修改被借書書架上書籍的狀態(tài)從0到1觸發(fā)器影響范圍BookBorrowReturn/LendroombookUSE LMS_xiliGO/* Object: Trigger dbo.aaa Script Date: 06/17/2011 08:55:14 */SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER trigger dbo.aaa on dbo.BookBorrowReturnfor insertASdeclare boo

33、kid numeric(18, 0) beginselect bookid = bookid from inserted;update Lendroombook set LendRoomBook.BookState = 1 where LendRoomBook.BookID =(select BookID from inserted);END1.22 花卉表中插入數(shù)據(jù)則更新花卉的狀態(tài)概述 當(dāng)向還書表中插入數(shù)據(jù)時(shí),則表示書本已歸還,這時(shí)修改被還書書架上書籍的狀態(tài)從1到0觸發(fā)器影響范圍BookBorrowReturn/lendroombookUSE LMS_xiliGO/* Object: Tri

34、gger dbo.lrstate Script Date: 06/17/2011 08:55:22 */SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER trigger dbo.lrstate on dbo.BookBorrowReturnfor updateas declare bookid numeric(18, 0) beginselect bookid = bookid from inserted;update lendroombook set lendroombook.bookstate = 0where lendroombook.

35、bookid=(select bookid from BookBorrowReturn where BookBorrowReturn.state = 0 and BookBorrowReturn.bookid=bookid) ;End1.23 花卉訂購(gòu)表中插入數(shù)據(jù)則更新訂購(gòu)花卉的狀態(tài)概述 當(dāng)向還書表中插入數(shù)據(jù)時(shí),則表示書本已歸還,這時(shí)修改被還書書架上書籍的狀態(tài)從1到0觸發(fā)器影響范圍BookBorrowReturn/lendroombook USE LMS_xiliGO/* Object: Trigger dbo.lrstate1 Script Date: 06/17/2011 08:55:28

36、 */SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER trigger dbo.lrstate1 on dbo.BookBorrowReturnfor updateas declare bookid numeric(18, 0) beginselect bookid = bookid from deleted;update lendroombook set lendroombook.bookstate = 0where lendroombook.bookid = bookid ;End1.24 用戶表中插入數(shù)據(jù)則表的狀態(tài)概述 當(dāng)向還書表中插入

37、數(shù)據(jù)時(shí),則表示書本已歸還,這時(shí)修改借書證被借觸發(fā)器影響范圍BookBorrowReturn/bookcardUSE LMS_xiliGO/* Object: Trigger dbo.xxx1 Script Date: 06/17/2011 08:55:34 */SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER trigger dbo.xxx1 on dbo.BookBorrowReturn for insertas declare cardid numeric(18, 0)beginselect cardid = cardid from inserted;update bookcard set bookcard.borrowed = bookcard.borrowed + 1 where id = cardid;end1.25 花卉表中插入數(shù)據(jù)則更新花卉表中花卉狀態(tài)概述 當(dāng)向借書表中插入數(shù)據(jù)時(shí),則表示書本已借出,這時(shí)修改書籍信息中書籍的狀態(tài)為1表示被借觸發(fā)器影響范圍LendRoom