The Asia Pacific University (APU) library has a huge collection of hardcopy books and e-books, however its small bookshop has limited its availability of books. The objective of this project is to design a database system for the online APU’s e-Bookstore. An e-Bookstore system allows APU to find and manipulate the information collected and stored easily and effectively. Additionally, it helps to overcome problems of traditional physical bookstores where there are limited spaces, fixed location, narrow sales channels and inconvenience to purchase books especially during a pandemic.
In this project, an entity relationship modelling will first be presented as the drafted model of a database system for APU e-Bookstore. Subsequently, business rules are illustrated to list down the relevant entities and attributes for an efficient database. Normalisation process will be performed to organise the data within the database. Later on, a database schema will be generated to see the bigger picture of the database. Moreover, a data dictionary with the definition of table names, attribute names, descriptions, data types, role of primary or foreign key and foreign reference table will be given for a better database management. Lastly, SQL statements with definition and manipulation languages will be shown in order to test the effectiveness of the database.
Below are the fifteen business rules applied for the APU e-Bookstore database:
- A Publisher can send a list that contains many Books to a Manager.
- A Manager can place zero, one or many Orders to one or many Publishers.
- An Order to Publisher with zero, one or many Items is associated with one Manager and one or many Publisher.
- An Item in the Order to Publisher can contain one or many Books.
- A Publisher can issue only one Invoice that contains one or many Items.
- An Item in the Invoice can contain one or many Books.
- Many Invoices are transferred to one Account Department.
- A Publisher can supply one or many Books to the bookstore.
- One or many Books on the list given by one Publisher and one or many Books in the bookstore are differentiated but associated to a Publisher as the books on the list may not necessarily be delivered to the bookstore yet.
- A Book in the bookstore is associated with many Categories.
- A Member can place zero, one or many Books in only one Shopping Cart that contains zero, one or many Items.
- One or many Books in the bookstore are associated with one or many Items in one Shopping Cart.
- A Member can have zero, one or many Orders that contain zero, one or many Items and an Order is made by one Member.
- One or many Books in the bookstore are associated with one or many Item in one Member’s Order.
- One Member can provide one or many Feedbacks and one Book is associated with many Feedbacks.
