1、 给出设计的表名、结构以及表上设计的完整性约束。student:列名数据类型是否为空/性质说明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书籍分类bo
2、ok_recorddatatimenull书籍登记日期book_sort: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
3、_phone管理员电话 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 Wa
4、rnings: 0 alter table student add index 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 bo
5、ok(book_record);1.为stu_id和book_id创建多列索引: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 a
6、sc);1. 为stu_id和book_id创建多列索引:create 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.在表stud
7、ent上创建计算机专业(cs)学生的视图stu_cs: create view stu_cs as select * from student where pro = cs;操作和结果: where stu_pro = cs;2. 在表student, borrow和book上创建借书者的全面信息视图stu_borrow: create view stu_borrow as select student.stu_id, book.book_id, student.stu_name, book.book_name, borrow_date,adddate(borrow_date,30) expe
8、ct_return_date from student, book, borrow where student.stu_id = borrow.stu_id and book.book_id = borrow.book_id;3.创建类别1的所有图书的视图cs_book: create view cs_book as from book where book.book_sort in (select book_sort.sort.name from book_sort where sort_id = 1);操作和结果显示: (select book_sort.sort_name4.创建个人所有
9、借书归还纪录视图stu_borrow_return: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、设计触发器
10、 给出在各表上建立的触发器以及使用的语句。1.设计触发器borrow, 当某学生借书成功后,图书表相应的图书不在架上,变为0: create trigger borrow after insert on borrow for each row begin update book set book_num = book_num 1 where book_id = new.book_id; end操作与结果显示: delimiter $ create trigger trigger_borrow - after insert on borrow update book set book_num =
11、 book_num - 1 where book_id = new.book_id; $在插入表borrow之前,book_id = 1 的图书还在架上,为1:学生1借了这本书后,在borrow中插入了一条记录:在borrow中插入这条记录后,book_id =1的图书,不在架上,为0:2.设计触发器trigger_return, 还书成功后,对应的书籍book_num变为1: create trigger trigger_return after insert on return_table update book set book_num = book_num + 1 还书时在return
12、_table插入表项:此时图书归还架上: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
13、event eventJob on completion preserve enable; /*开启定时器*/1). 学生1借了图书1,生成借书记录stu_borrow视图,如下:2). 当他在1月27日前还书时,没有生成罚单:3). 当他在1月27日后还书时,生成罚单:4.设计触发器trigger_credit,若处罚记录超过30条,则将这个学生的诚信级设置为0,下次不允许借书: create trigger trigger_credit after insert on ticket for each row if (select count(*) from ticket where stu
14、_id=new.stu_id)30 then update 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中,
15、这个存储过程在定时器eventJob中调用:sql:create procedure proc_gen_ticket(in currentdate datetime)BEGIN declare 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,s
16、tu_borrow.expect_return_date) from stu_borrow where cur_datestu_borrow.expect_return_date;end2.设计学生注册信息存储过程:学生注册信息stu_register create 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 st
17、udent(stu_id, stu_name, stu_sex, stu_age, stu_pro, stu_grade) values(stu_id, stu_name, stu_sex, stu_age, stu_pro, stu_grade);3. 设计管理员注册信息存储过程:ma_register create procedure ma_register(in ma_id int, in ma_name varchar(20), in ma_age int, in ma_phone int)BEGIN insert into manager values(ma_id, ma_name,
18、 ma_age, ma_phone);END4. 借书过程的实现:1)设计存储函数,func_get_credit,返回学生的诚信级:create function func_get_credit(stu_id int) returns intbeginreturn(select stu_integrity from student where student.stu_id = stu_id);2)设计存储函数,func_get_booknum,返回书籍是否在架上:create function func_get_booknum(book_id int) returns int return(
19、select book_num from book where book.book_id = book_id);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(bo
20、ok_id) = 1 then insert into borrow values(stu_id, book_id, borrow_date); else select failed to borrow end if; 实验操作与结果显示: borrow纪录为空: 执行函数,学生1借图书2: call proc_borrow(1,2,now(); 学生1的诚信级为0: 借书失败: 修改学生1诚信级为1: 此时借书成功:5. 还书存储过程proc_return:当还书时,查看是否书是否超期,即查询ticket表项,当发现超期,提示交罚单后再次还书,如没有超期,则纪录归还项目到return_tab
21、le中,并且删除借书纪录(以免还书后定时器仍然扫描这个纪录): create procedure proc_return(in stu_id int, 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表示没有交*/ select please pay off the ticket else /*纪录归还项目到return_table中,并且删除借书纪录*/ set borrowdate = (select borrow_date from borrow where borrow.stu_id = stu_id and borrow.book_id = book_id); insert into return_table values(stu_id