数据库综合作业(图书管理系统)

图书馆数据管理系统一、图书馆数据管理系统的描述1、概念模型（E-R图）2、关系模式(1)书籍类别（种类编号、种类名称）(2)读者（借书证编号，读者姓名、读者性别、读者种类、登记时间）(3)书籍（书籍编号、书籍名称、书籍类别、书籍作者、出版社名称、出版日期、登记日期）(4)借阅（借书证编号、书籍编号、读者借书日期）(5)还书（借书证编号、书籍编号、读者还书时间）(6)罚款（借书证编号、读者姓名、书籍编号、读者借书时间、读者还书日期）二、图书馆数据库的建立--(1)书本类别表createtablebook_style(bookstylenovarchar(30)primarykey,--种类编号bookstylevarchar(30))--种类名称go--(2)书库表createtablesystem_books(bookidvarchar(20)primarykey,--书籍编号booknamevarchar(30)notnull,--书籍名称bookstylenovarchar(30)notnull,--书籍种类bookauthorvarchar(30),--书籍作者bookpubvarchar(30),--出版社bookpubdatedatetime,--出版日期bookindatedatetime,--登记日期isborrowedvarchar(2),--是否借出foreignkey(bookstyleno)referencesbook_style(bookstyleno))go--(3)借书证表createtablesystem_readers(readeridvarchar(9)primarykey,--读者借书证号readernamevarchar(9)notnull,--读者姓名readersexvarchar(2)notnull,--读者性别readertypevarchar(10),--读者种类regdatedatetime)--登记日期go--(4)借书记录表createtableborrow_record(bookidvarchar(20)primarykey,--书籍编号readeridvarchar(9),--读者借书证编号borrowdatedatetime,--读者借书时间foreignkey(bookid)referencessystem_books(bookid),foreignkey(readerid)referencessystem_readers(readerid))go--(5)还书记录表createtablereturn_record(bookidvarchar(20)primarykey,--书籍编号readeridvarchar(9),--读者借书证编号returndatedatetime,--读者还书时间foreignkey(bookid)referencessystem_books(bookid),foreignkey(readerid)referencessystem_readers(readerid))go--(6)罚款单表createtablereader_fee(readeridvarchar(9)notnull,--读者借书证号readernamevarchar(9)notnull,--读者姓名bookidvarchar(20)primarykey,--书籍编号booknamevarchar(30)notnull,--书籍名称bookfeevarchar(30),--罚款金额borrowdatedatetime,--借书时间foreignkey(bookid)referencessystem_books(bookid),foreignkey(readerid)referencessystem_readers(readerid))go数据库的表的截图:三、图书馆数据库数据的添加uselibrary--书籍类别导入book_style表中insertintobook_style(bookstyleno,bookstyle)values('1','恐怖小说')insertintobook_style(bookstyleno,bookstyle)values('2','穿越小说')insertintobook_style(bookstyleno,bookstyle)values('3','恐怖小说')insertintobook_style(bookstyleno,bookstyle)values('4','都市小说')insertintobook_style(bookstyleno,bookstyle)values('5','科幻小说')insertintobook_style(bookstyleno,bookstyle)values('6','仙侠小说')insertintobook_style(bookstyleno,bookstyle)values('7','言情小说')--已有书籍导入system_books表中insertintosystem_books(bookid,bookname,bookstyleno,bookauthor,bookpub,bookpubdate,bookindate,isborrowed)values('20185678901','飘邈之旅','1','萧乾','向往','2015-09-01','2018-03-25','1')insertintosystem_books(bookid,bookname,bookstyleno,bookauthor,bookpub,bookpubdate,bookindate,isborrowed)values('20185678902','明朝那些事儿','2','多一半','新星出版社','2015-05-09','2018-05-23','1')insertintosystem_books(bookid,bookname,bookstyleno,bookauthor,bookpub,bookpubdate,bookindate,isborrowed)values('20185678903','鬼吹灯','3','天下霸唱','安徽文艺出版社','2014-09-18','2018-05-27','1')insertintosystem_books(bookid,bookname,bookstyleno,bookauthor,bookpub,bookpubdate,bookindate,isborrowed)values('20185678904','盛夏晚晴天','4','顾漫','中国海关出版社','2015-09-01','2018-05-28','1')insertintosystem_books(bookid,bookname,bookstyleno,bookauthor,bookpub,bookpubdate,bookindate,isborrowed)values('20185678905','三体','5','刘慈欣','凤凰出版社','2015-10-11','2018-05-29','1')insertintosystem_books(bookid,bookname,bookstyleno,bookauthor,bookpub,bookpubdate,bookindate,isborrowed)values('20185678906','凡人修仙传','6','三十','凡人出版社','2013-04-21','2018-05-30','1')insertintosystem_books(bookid,bookname,bookstyleno,bookauthor,bookpub,bookpubdate,bookindate,isborrowed)values('20185678907','霸道总裁爱上我','7','桐华','民族出版社','2012-06-20','2018-05-31','1')--将已有图书证的读者加入system_readers表中insertintosystem_readers(readerid,readername,readersex,readertype,regdate)values('Q20170401','白一','男','学生','2018-01-1812:20')insertintosystem_readers(readerid,readername,readersex,readertype,regdate)values('Q20170402','白二','男','学生','2018-01-1913:15')insertintosystem_readers(readerid,readername,readersex,readertype,regdate)values('Q20170403','白三','男','学生','2018-01-2013:33')insertintosystem_readers(readerid,readername,readersex,readertype,regdate)values('Q20170404','白四','男','学生','2018-01-2112:01')insertintosystem_readers(readerid,readername,readersex,readertype,regdate)values('Q20170405','白五','男','学生','2018-01-2215:23')insertintosystem_readers(readerid,readername,readersex,readertype,regdate)values('201005','黑五','男','教师','2018-01-2318:50')insertintosystem_readers(readerid,readername,readersex,readertype,regdate)values('201006','黒六','男','教师','2018-01-2418:25')insertintosystem_readers(readerid,readername,readersex,readertype,regdate)values('GL001','空一','女','管理','2018-01-0116:20')--添加已借书读者的记录导入borrow_record表中，同时将在已借出的标记0insertintoborrow_record(bookid,readerid,borrowdate)values('20185678901','Q20170401','2018-01-1812:20')updatesystem_bookssetisborrowed=0wherebookid='20185678901'andisborrowed='1'insertintoborrow_record(bookid,readerid,borrowdate)values('20185678902','Q20170402','2018-01-1913:15')updatesystem_bookssetisborrowed=0wherebookid='20185678902'andisborrowed='1'insertintoborrow_record(bookid,readerid,borrowdate)values('20185678903','Q20170403','2018-01-2013:33')updatesystem_bookssetisborrowed=0wherebookid='20185678903'andisborrowed='1'insertintoborrow_reco