數(shù)據(jù)庫(kù)圖書管理系統(tǒng)課程設(shè)計(jì)(完整版)
【正文】 yXPq5 foreign key (readerid references system_readers(readerid 定義借書證號(hào)為外鍵6ewMyirQFL索引地創(chuàng)建create index borrow_record_index_readerid on borrow_record(readerid—為借閱表在借書證號(hào)上建索引kavU42VRUscreate index reader_fee_index_readerid on reader_fee (readerid—為罰款表在借書證號(hào)上建索引y6v3ALoS89create index return_record_index_readerid on return_record(readerid 為歸還表在借書證號(hào)上建索引M2ub6vSTnPcreate index system_books_index_bookname on system_books(bookname—為圖書信息表在書籍編號(hào)上建索引0YujCfmUCwcreate index system_books_index_bookauthor on system_books(bookauthor為圖書信息表在作者上建索引eUts8ZQVRd創(chuàng)建視圖create view b1_system_booksas select bookid,bookstyle,bookpub,booknamefrom system_bookscreate view r1_system_readersas select readerid,readername,readertypefrom system_readerscreate view b2_borrow_recordas select bookid, readeridfrom borrow_record建立借書記錄存貯過(guò)程create procedure sp_borrowbook bookid char(20, readerid char(9 asinsert into borrow_record (bookid,readerid,borrowdatesQsAEJkW5Tvalues(bookid,readerid,getdate(go建立還書記錄存貯過(guò)程create procedure sp_returnbook bookid char(20,readerid char(9GMsIasNXkAasdelete from retun_recordwhere bookid=bookid and readerid=readeridgo建立罰款單存貯過(guò)程create procedure sp_ readerfee readerid char(9, borrowdate datetimeTIrRGchYzgasupdate reader_feeset bookfee=*(day(getdate(day(borrowdatewhere reader=readeridand borrowdate=borrowdate建立借書記錄表增加觸發(fā)器create trigger [tr_addlend] on borrow_recordfor insertasupdate system_booksset isborrowed =1where bookid in (select bookid from insertedupdate readerset borrowednum = borrowednum +1
公司管理相關(guān)推薦
鄂ICP備17016276號(hào)-1