Homework answers / question archive / AICT005-4-1 Database Systems Case Study: APU’s E-Bookstore The availability of books and reading material for purchase within the Asia Pacific University (APU) is quite inadequate

AICT005-4-1 Database Systems Case Study: APU’s E-Bookstore The availability of books and reading material for purchase within the Asia Pacific University (APU) is quite inadequate

Computer Science

Share With

---

AICT005-4-1 Database Systems

Case Study:

APU’s E-Bookstore

The availability of books and reading material for purchase within the Asia Pacific University (APU) is quite inadequate. Although the APU library has vast collection of books (both hardcopy and e-books), the availability of it is quite limited and bound by many restrictions.  Student and staffs only have the option of a small bookshop within the enterprise. Larger books store in the city are often sought for other varieties.

In view of the growing population within APU, the university is planning to establish an e-bookstore. The online store will facilitate the purchase of latest books of many genres. Your team is assigned the project to design and implement a database system for online APU’s e-Bookstore System.

 

Scenario:

• Publishers frequently send lists of latest books to the e-bookstore manager. The bookstore manager compiles a list of needed books and sends an order to the publishers. The publisher sy. The bookstore manager records the details of the order and books that have arrived at the bookstore.
• Customers, who wish to purchase books online, need to initially register as members. Members will be able to view the book, read reviews and compare the online products with other similar ones.
• Members who wish to purchase can select their books into the website’s shopping cart. The cart will show the summary of the selection and total cost to be paid. Once the payment is made, the order is confirmed, the bookstore will send the books to the customers within 7 working days.
• The system should manage information about books in the bookstore, members and books they have ordered. It should also store information about members’ feedback.                  
• Members can also provide 'rating' for a book, as a score (1-10 which is 1 = terrible, 10= masterpiece) along with optional short text. No changes are allowed; only one feedback per user per book is allowed.

 

 

*Note: you may make any other assumptions after discussing them with module lecturer

 

Coursework Details:

 

1. In this assignment, you are required to design, implement, and document a database system for an electronic bookstore named as APU E-Bookstore.

 

2. Create the following queries using Data Manipulation Language (DML) – student must be able to explain the queries.

 

1. 1. List the book(s) which has the highest rating. Show book id, book name, and the rating.

 

1. 2. Find the total number of feedback per member. Show member id, member name, and total number of feedback per member.

 

1. 3. Find the total number of book published by each publisher. Show publisher id, publisher name, and number of book published.

 

1. 4. Find the total number of books ordered by store manager from each publisher.

 

1. 5. Find the total number of books ordered by each member.

 

1. 6. Find the bestselling book(s).

 

1. 7. Show list of total customers based on gender who are registered as members in APU E-Bookstore. The list should show total number of registered members and total number of gender (male and female).

 

1. 8. Show a list of purchased books that have not been delivered to members. The list should show member identification number, address, contact number, book serial number, book title, quantity, date and status of delivery.

 

1. 9. Show the member who spent most on buying books. Show member id, member name and total expenditure.

 

1. 10. Show a list of total books as added by each members in the shopping cart.

 

1. 11. Find the total number of database books that were sold in April-2020.
   12. Show list of books that were added to the shopping but the order was not completed. Show member id, member name, book serial number, and book title.

 

 

 

 

3. Deliverables - Minimum requirement of your documentation:

 

Part	Database Schema Finalized ERD using chen’s or crow’s foot notation Implement the database design into the DBMS Generate the database diagram from the DBMS
1	SQL-Data Definition Language (DDL) Create all tables with suitable data types Insert 5-10 rows of data into each table Screen shot all tables with its data Screen shot all query statements
1	SQL-Data Manipulation Language (DML) Write SQL statements to answer question (2) above Screen shot all query statements together with its executed result
1
2
2
2

 

 

General Requirements:

 

In this assignment you are required to:

• Work a group of 3-4 members.
• Design and implement a solution to a business problem.
• Implement the solution using any Enterprise DBMS.
• Document the solution as set out in the assignment requirements.
• Submit the document online according to the date and time given below.
• Submit a Workload Matrix given by lecturer through Ms.TEAMS.
• Each group member is required to participate in all tasks / discussions together.
• Presentation schedules will be published at a later date through Ms.TEAMS.

 

Note: It is acceptable for discrete activities of this assignment to be undertaken by individual group members. However, it is essential that all group members understand the presentation in its entirety. At the end of the demonstration your group will be asked a series of questions to explore your understanding and analysis of the given problem.   Responses to these questions such as “I don’t know because I didn't work on that part of the assignment” are not acceptable and will result in a penalty for either the entire group or specific individual(s).

 

 

 

Part	Assessment Criteria:	Marks Allocation	Online Submission Date
1	Group Component (40%) Database and Database Management System                                             Business Rules & Normalization                                             Entity Relationship Diagram	8% 12% 20%
2	Group Component (18%) Database Schema Individual Component (42%) SQL-Data Definition Language (DDL) SQL-Data Manipulation Language (DML)	18%   12% 30%