Answered step by step
Verified Expert Solution
Question
1 Approved Answer
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
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 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 which is terrible, masterpiece along with optional short text review. No changes are allowed and only one reviewrating per user per book is allowed.
Note: you may make any other assumptions after discussing them with module lecturer.
Coursework Details:
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.
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
a Show the books in the shopping cart for each member which have not been checkout
b List the books 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
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
a Find the genres of the book which has the most number of book quantity in stock
b Find the bestselling books
c Show the members who spent most on buying books.
Student
a Show the members 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 orders.
Deliverables Minimum requirement of your documentation:
Part Component
a Database and Database Management System
Discuss the disadvantages of filebased 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
b Business Rules & Normalization
Provide a list of business rules
Provide an example of UNF according to case study, then perform normalization up to NF clearly showing all the steps with explanation
c Entity Relationship Diagram
Design the database using Chens or Crows 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.
d Database Schema
Resubmit the ERD, you may make changes to the ERD submitted in Part
Generate the database diagram from the DBMS
e SQLData Definition Language DDL
Create all tables with suitable data types
Insert minimum rows of data into each table.
Screen shot all tables with its data
Screen shot all query statements
f SQLData Manipulation Language DML
Write SQL statements to answer question above
Screen shot all query statements together with its executed result
g Presentation
General Requirements:
In this assignment you are required to:
Work in a group of 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
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started