A bookstore located in Kuala Lumpur Malaysia has vast collection of books in its warehouse. The bookstore ordered books of various genres from different publishers and sell them to the customers. As their business grows, the bookstore would like to implement an online store to facilitate the purchase of books to more customers. Your team is assigned to design and implement a database system for Online Bookstore Database System.

Scenario:

$$ Publishers frequently send lists of latest books to the bookstore manager. The bookstore manager compiles a list of needed books and sends an order to the publishers. The publisher supplies the ordered books to the bookstore. The bookstore manager records the details of the books that have arrived at the bookstore. The orders sent to publishers need to be recorded in the database

$$ Customers, who wish to purchase books online, need to initially register as members. Members will be able to view the book, read reviews and provide review for the books.

$$ Members who wish to purchase can select their books into the 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 $5$ working days.

$$ The system should manage information about books in the bookstore, members and books they have ordered as well as payment details and delivery status.

$$ Members can also provide 'rating' for a book, as a score $(1-5$ which is $1=$ terrible, $5=$ masterpiece$)$ along with optional short text review. No changes are allowed and only one review$/$rating 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 online bookstore. You can name the store based on your creativity.

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

$($Hint: Your sample data provided should be able to derive all the queries as below$)$

Student $1$

a$)$ Show the books in the shopping cart for each member which have not been check$-$out

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

c$)$ Find the total number of books published by each publisher. Show publisher id$,$ publisher name, and number of books published.

Student $2$

a$)$ Find the total number of feedbacks per member. Show member id$,$ member name, and total number of feedbacks per member.

b$)$ Find the publisher who published the most number of books

c$)$ Find the total number of books ordered by store manager from each publisher.

Student $3$

a$)$ Find the genres of the book which has the most number of book quantity in stock

b$)$ Find the bestselling book$($s$).$

c$)$ Show the member$($s$)$ who spent most on buying books.

Student $4$

a$)$ Show the member$($s$)$ who had not make any order

b$)$ A list of purchased books that have not been delivered to members.

c$)$ Show the members who made more than $2$ orders.

$3.$ Deliverables $-$ Minimum requirement of your documentation:

Part Component

$1$ a$)$ Database and Database Management System

$$ Discuss the disadvantages of file$-$based system, relate your discussion to the case study

$$ Discuss the advantages of Database and DBMS$,$ functions of DBMS$,$ relate your discussion to the case study

$1$ b$)$ Business Rules & Normalization

$$ Provide a list of business rules

$$ Provide an example of UNF according to case study, then perform normalization up to $3$NF clearly showing all the steps with explanation

$1$ c$)$ Entity Relationship Diagram

$$ Design the database using Chen$$s or Crow$$s foot notation. Draw the ERD with any suitable tools such as draw.io$.$ All entities, attributes, relationship and constraints should be shown in the diagram.

$2$ d$)$ Database Schema

$$ Re$-$submit the ERD, you may make changes to the ERD submitted in Part $1$

$$ Generate the database diagram from the DBMS

$2$ e$)$ SQL$-$Data Definition Language $($DDL$)$

$$ Create all tables with suitable data types

$$ Insert minimum $10$ rows of data into each table.

$$ Screen shot all tables with its data

$$ Screen shot all query statements

$2$ f$)$ SQL$-$Data Manipulation Language $($DML$)$

$$ Write SQL statements to answer question $(2)$ above

$$ Screen shot all query statements together with its executed result

$2$ g$)$ Presentation

General Requirements:

In this assignment you are required to:

$$ Work in 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.

$$ Each group member is required to participate in all tasks $/$ discussions together.

$$ Presentation schedule