2012年江西财大数据库作业（硕士）

罚款金额 （forfeit） Char(10) 3位随机整数 012 (2)

类似于实体集“借阅归还记录”和联系集“借阅图书”、“读者借阅”可画出实体集“预订记录”和联系集“预订图书”、“读者预订”。

分类 图书 写作 作者表 设置 图书分类表 借阅图书 办理借书 处罚单 借阅归还记录办理还书 读者分类表 读者借阅 关键词表 处罚 管理员 分类 读者 （3）

图书分类号：Bookclass (classNo, className)

图书：Book (ISBN, classNo, bookName, authorNo, keywordNo, publishingName,

price, publishingDate, shopDate, shopNum, retainedNum)

作者：Author (authorNo, authorName, sex, workunit) 图书作者：BookAuthor (authorNo, ISBN) 关键词：Keyword (keywordNo, keyword) 图书关键词：BookKeyword (keywordNo, ISBN)

借阅归还记录：Borrow (borrowNo, readerNo, ISBN, borrowDate, shouldDate,

returnDate, borrowEmployeeNo, returnEmployeeNo)

读者：Reader (readerNo, readerName, sex, identitycard, workunit, readerClassNo)

读者分类：ReaderClass (readerClassNo, readerClass, borrowNumLimit, borrowTimeLimit)

—— readerClass的取值有：”教师”、”职工”、”本科生”、”研究生”等 图书管理员：Employee (employeeNo, employeeName, sex, salary)

处罚单：Ticket (ticketNo, ticketClass, ticketAmount, borrowNo, ticketEmployeeNo)

—— ticketClass的取值有：”逾期归还”、”丢失赔偿”、”丢失赔书”等 （4）CREATE VIEW Utilization

As

SELECT year, b.ISBN, bookName, shopNum, borrowNum, borrowNum/shopNum AS avgNum

FROM Book AS b,

( SELECT year(borrowDate) AS year, ISBN, count(*) AS borrowNum

FROM Borrow

GROUP BY year(borrowDate), ISBN ) AS a WHERE a.ISBN=b.ISBN