1、数据类型是否为空/性质说明stu_idintnot null /PK标明学生唯一学号stu_namevarcharnot null学生姓名stu_sex学生性别stu_age学生年龄stu_pro学生专业stu_grade学生年级stu_integritynot null/default=1学生诚信级book:book_idnot null / PK唯一书籍序号book_name书籍名称book_author书籍作者book_pub书籍出版社book_numint not null 书籍是否在架上book_sort书籍分类book_recorddatatimenull书籍登记日期book_so
2、rt:sort_id类型编号sort_name类型名称borrow:存储学生的借书信息student_id学生编号varchar 书籍编号borrow_date借书时间expect_return_datedatetime预期归还时间return_table:存储学生的归还信息return_date实际还书时间ticket:存储学生的罚单信息over_date超期天数ticket_feefloat处罚金额manager:manager_id管理员编号manager_name管理员姓名manager_age管理员年龄manager_phone管理员电话3、设计索引给出在各表上建立的索引以及使用的语
3、句。1.为stu_id创建索引，升序排序sql:create index index_id on student（stu_id asc）;2.为stu_name创建索引，并且降序排序alter table student add index index_name（stu_name, desc）;插入索引操作和结果如下所示：mysql create index index_id on student（stu_id asc）;Query OK, 0 rows affectedRecords: 0 Duplicates: 0 Warnings: 0 alter table student add i
4、ndex index_name（stu_name desc）;1.为book_id创建索引，升序排列create index index_bid on book（book_id）;2.为book_record创建索引，以便方便查询图书的登记日期信息，升序：create index index_brecord on book（book_record）;插入索引的操作和结果如下所示： create index index_bid on book（book_id）; create index index_brecord on book（book_record）;1.为stu_id和book_id创建
5、多列索引：create index index_sid_bid on borrow（stu_id asc, book_id asc）; create index index_sid_bid on borrow（stu_id asc, book_id asc）;create index index_sid_bid on return_table（stu_id asc, book_id asc）; create index index_sid_bid_r on return_table（stu_id asc, book_id asc）;1. 为stu_id和book_id创建多列索引：create
6、 index index_sid_bid on ticket（stu_id asc, book_id asc）; create index index_sid_bid on ticket（stu_id asc, book_id asc）;1.为manager_id创建索引：create index index_mid on manager（manager_id）; create index index_mid on manager（manager_id）;4、设计视图给出在各表上建立的视图以及使用的语句。1.在表student上创建计算机专业（cs）学生的视图stu_cs： create vi
7、ew stu_cs asselect *from studentwhere pro = cs;操作和结果： create view stu_cs aswhere stu_pro = cs;2. 在表student, borrow和book上创建借书者的全面信息视图stu_borrow： create view stu_borrow asselect student.stu_id, book.book_id, student.stu_name, book.book_name, borrow_date，adddate（borrow_date,30） expect_return_datefrom s
8、tudent, book, borrowwhere student.stu_id = borrow.stu_id and book.book_id = borrow.book_id;3.创建类别1的所有图书的视图cs_book： create view cs_book asfrom bookwhere book.book_sort in（select boofrom book_sortwhere sort_id = 1）;操作和结果显示： create view cs_book as（select book_sort.sort_name4.创建个人所有借书归还纪录视图stu_borrow_re
9、turn:create view stu_borrow_return asselect student.stu_id, student.stu_name, book.book_id, book.book_name,return_table.borrow_date,return_table.return_datefrom student, book, return_tablewhere student.stu_id = return_table.stu_id and book.book_id = return_table.book_id;5、设计触发器给出在各表上建立的触发器以及使用的语句。1.
10、设计触发器borrow, 当某学生借书成功后，图书表相应的图书不在架上，变为0：create trigger borrowafter insert on borrowfor each rowbeginupdate book set book_num = book_num 1where book_id = new.book_id;end操作与结果显示： delimiter $ create trigger trigger_borrow- after insert on borrow for each row begin update book set book_num = book_num -
11、1 where book_id = new.book_id; end $在插入表borrow之前，book_id = 1 的图书还在架上，为1：学生1借了这本书后，在borrow中插入了一条记录：在borrow中插入这条记录后，book_id =1的图书，不在架上，为0：2.设计触发器trigger_return, 还书成功后，对应的书籍book_num变为1：create trigger trigger_returnafter insert on return_tableupdate book set book_num = book_num + 1还书时在return_table插入表项：此
12、时图书归还架上：3.定义定时器（事件）eventJob，每天自动触发一次，扫描视图stu_borrow，若发现当前有预期归还时间小于当前时间，则判断为超期，生成处罚记录，这个定时器将每天定时触发存储过程proc_gen_ticket：create event if not exists eventJobon schedule every 1 DAY /*每天触发*/on completion PRESERVEdo call proc_gen_ticket（getdate（）; /*调用存储过程*/set global event_scheduler = 1;alter event eventJ
13、ob on completion preserve enable; /*开启定时器*/1）. 学生1借了图书1，生成借书记录stu_borrow视图，如下：2）. 当他在1月27日前还书时，没有生成罚单：3）. 当他在1月27日后还书时，生成罚单：4.设计触发器trigger_credit，若处罚记录超过30条，则将这个学生的诚信级设置为0，下次不允许借书：create trigger trigger_creditafter insert on ticketif （select count（*） from ticket where stu_id=new.stu_id）30 thenupdate
14、 student set stu_integrity = 0 where stu_id = new.stu_id;end if;操作和结果显示，测试时选择插入ticket项大于3，因为30太大了，不容易测试：学生1超过3次超期归还图书后，产生了4条罚单：此时触动触发器trigger_credit，将学生1的诚信级设置为0：四、应用程序设计与编码实现1、系统实现中存储函数和存储过程的设计要求给出功能描述和代码。1. 设计存储过程，产生罚单proc_gen_ticket：当日期超过预定归还日期时，产生罚单，并将记录写入表ticket中，这个存储过程在定时器eventJob中调用：sql：creat
15、e procedure proc_gen_ticket（in currentdate datetime）BEGINdeclare cur_date datetime;set cur_date = currentdate;replace into ticket（stu_id, book_id, over_date, ticket_fee）select stu_id, book_id, datediff（cur_date,stu_borrow.expect_return_date）,0.1*datediff（cur_date,stu_borrow.expect_return_date）from s
16、tu_borrowwhere cur_datestu_borrow.expect_return_date;2.设计学生注册信息存储过程：学生注册信息stu_registercreate procedure stu_register（in stu_id int, in stu_name varchar（20）, in stu_sex varchar（20）, in stu_age int, in stu_pro varchar（20）, in stu_grade varchar（20）insert into student（stu_id, stu_name, stu_sex, stu_age,
17、stu_pro, stu_grade）values（stu_id, stu_name, stu_sex, stu_age, stu_pro, stu_grade）;3. 设计管理员注册信息存储过程：ma_registercreate procedure ma_register（in ma_id int, in ma_name varchar（20）, in ma_age int, in ma_phone int）insert into managervalues（ma_id, ma_name, ma_age, ma_phone）;END4. 借书过程的实现:1） 设计存储函数，func_get
18、_credit，返回学生的诚信级：create function func_get_credit（stu_id int） returns intreturn（select stu_integrity from student where student.stu_id = stu_id）;2） 设计存储函数，func_get_booknum，返回书籍是否在架上：create function func_get_booknum（book_id int） returns intreturn（select book_num from book where book.book_id = book_id）
19、;3） 设计存储过程proc_borrow，调用func_get_credit和func_get_booknum，判断这个学生诚信度和书籍是否在架上，若为真，则借书成功，在borrrow表中插入纪录；否则提示失败：create procedure proc_borrow（in stu_id int, in book_id int, in borrow_date datetime）if func_get_credit（stu_id） = 1 and func_get_booknum（book_id） = 1 theninsert into borrowvalues（stu_id, book_id
20、, borrow_date）;elseselect failed to borrow实验操作与结果显示：borrow纪录为空：执行函数，学生1借图书2：call proc_borrow（1,2,now（）;学生1的诚信级为0：借书失败：修改学生1诚信级为1：此时借书成功：5. 还书存储过程proc_return：当还书时，查看是否书是否超期，即查询ticket表项，当发现超期，提示交罚单后再次还书，如没有超期，则纪录归还项目到return_table中，并且删除借书纪录（以免还书后定时器仍然扫描这个纪录）：create procedure proc_return（in stu_id int,
21、in book_id int, in return_date datetime）DECLARE borrowdate datetime;if （select payoff from ticket where ticket.stu_id = stu_id and ticket.book_id=book_id） = 1 then /*判断是否交了罚单，1表示没有交*/please pay off the ticketelse /*纪录归还项目到return_table中，并且删除借书纪录*/set borrowdate = （select borrow_date from borrow where borrow.stu_id = stu_id and borrow.book_id = book_id）;insert into return_tablevalues（stu_id, book_id, borrowdate, return_date）;delete from borrowwhere borrow.stu_id = stu_id and borrow.book_id = book_id;学生1借了图书2：超期产生
