Advanced Database Management IOM205

Context

UniBooks is a small start-up company specialising in second-hand textbooks. The company buys  surplus academic books in bulk from publishers but also buys individual books from students. The books are advertised and sold via the company’s website. You have been approached to develop

a database system for the company. The system needs to be able to store details of the books held in stock, to process incoming books and to record all sales. The system will also need to be able to perform other functions, such as producing invoices and different management reports.

iuww520iuww520iuww520iuww520iuww520iuww520iuww520iuww520

Specification

UniBooks supplies textbooks to university students at discounted prices. The company obtains stocks of books directly from publishers and students:

• Occasionally, a publisher will decide to dispose of surplus books. As an example, when a new edition of a popular text is released, the publisher may decide to get rid of any remaining stocks of the previous edition. In such cases, UniBooks is often able to purchase large stocks of books from publishers for as little as 10% of the cover price.

• After completing a course, a student may wish to sell his/her unwanted textbooks.

UniBooks buys textbooks directly from students for 20% of the cover price.

• Some books (e.g. print on demand titles) are ordered directly from the publisher whenever an order from a customer is received. These books are purchased at 10% of the cover price of the printed version.

• All books are sold to the public for 50% of the cover price. There is also a standard charge of ￥ 10 for postage and packaging, regardless of the size of the order placed.

The current system suffers from several limitations:

• Occasionally, a customer will order a book that is not held in stock (e.g. print on demand titles). A staff member of the dispatch team will need to search for details of the book’s

publisher, and then contact the publisher to see if UniBooks can obtain a copy of the book.

The current system does not provide a search facility that can be used to locate a publisher’s details.

• When stocks of a given book run out, it is left to a member of the dispatch team to reorder a fresh supply from publishers. This raises two problems: customer orders are delayed until new stocks of the book arrive, and there is inconsistency regarding the number of books ordered from publishers. As an example, sometimes too few or too many books are ordered, resulting in further delays to customers or increased inventory costs. Ideally, it should be possible to set reorder levels and reorder quantities for books to improve inventory control.

• Management information takes a great deal of time to produce since it is

collected/processed manually. As a result, managers are only able to see important information, such as sales figures, on a monthly basis. Ideally, management information should be available on demand.

Xi’an Jiaotong-Liverpool University

PAPERCODE:IOM414/20-21/S2/Resit CourseworkReport

Page 3 of 8

• Most publishers offer discounts based on order size. In general, discounts are given as shown below in Table 1. At present, UniBooks often fails to claim the discounts it is  to.