IT 330 Milestone Two Guidelines and Rubric In Module Four, submit a systems analysis for your project, including a comprehensive entity-relationship diagram (ERD).

Books’ R’ Us Database Proposal

Name

University

Books’ R’ Us Database Proposal

Overview

Books’ R’Us is a bookstore based out of New England that is owned by Tom and Sarah Smith. The bookstore sells both new and used books and has been able to keep up and withstand the challenges of larger bookstore chains, as well as compete with the internet giant Amazon. Their store has been able to face these challenges by offering new and used books, other products all at reasonable prices and at a comfortable location. Their location also offers a café and Wi-Fi which is highly appealing to many avid book readers. The Smiths have also recently acquired another local bookstore called Great Books, U.S.A. Each of these bookstores currently has a simple bookkeeping system in place. Due to their growing business, the Smiths have hired a consulting firm to help create a database that will allow them to keep track of bookstores inventory, sales, customer data, and other critical data.

Justification

The purpose of this project is to provide the Smiths with an organized and precise way of managing their two bookstores. With the completion of this project, the owners would have a combined database that would help them manage all of the relevant information regarding their current stores and any future stores from one location. This database would assist in resolving the problem of improper inventory count. It would also allow the company to keep better track of their sales and customer data. A database would also help the Smiths keep track of all of their customer information which would help show trends in sales. If the Smiths were to keep things as they were then, they would have to put in twice as much work to make sure that inventories were kept up to date. The owners could lose the sales from the online sales, Worst of all though for any business owner is that without the database they could end up spending more money instead of making it. 

Requirements

The main feature that this database will need to do is combine the two-store independent bookkeeping data into one primary system this will help the Smiths maintain accurate data to ensure success in their growing business. The Smiths want to be able to keep track of data that will assist them in making financial and marketing decisions. With one primary database, the Smiths will be able to retrieve valuable information, cross reference different pieces of information and perform searches based on specific queries with ease. One particular feature of this database would be a sales tracker this would help the company search and keep track of all books that were sold. It would also tell them what publishers those books were sold. It would assist them in making sure that they received their discounts when ordering books from specific publishers later. Another feature would be book search; this would allow customers to look up whatever book they were looking for and be told exactly where to find it in stores. It would also help employees when it came to fulfilling in-store pick up orders. A book search would also tell a customer which store the book they are looking for is at. A customer search would be a useful tool for the company as well so that employees would be able to find significant information regarding their clients. It would come in handy if a client were waiting for a particular book to get back in so that the employee could let the customer know their book had arrived. It would also be handy in keeping track of the sales by specific customers so that the Smiths could send out coupons or special promotion flyers to their most loyal customers. A popularity tracker would be another feature that would be beneficial to the Smiths because this would help them keep track of what their most popular titles, authors, and publishers are. It would assist them in because they could make sure that they keep more of the popular items in stock which would maintain their sales high. All of these features would contribute to ensuring that the Smiths stayed up on top with their competition and would help maintain their customers happy and coming back.

Design Considerations

The following is a breakdown of the entities and attributes that should be included in the database design.

The proposed database design includes nine different tables. The entities of these tables are listed in bold lettering. The entities of these tables are a store, employee, general products, customer, books, payment information, inventory, order detail, and order. In the diagram above the primary keys are highlighted in bright yellow. The foreign keys are highlighted with light yellow. With this database, Tom and Sarah Smith will be able to track the data of both stores with one database while still being able to query necessary data for each store.

A comprehensive entity-relationship diagram (ERD)

Customer

Books

Purchase order

                                                                            Supply order                             

   Customer name                                          Book title

Customer Address                               PublisherBook author

 Street                                      Name                   Year of Publication

                                                              Address

State                                                                Phone numberPublisher

                                                              Email address

  Zip code                                                         Genre

 Phone number

                                     E-commerce                  order delivery          Shipping details

  Email address

The above diagram illustrates how different entities in Tom and Sarah’s bookstore relate. The primary entities which are enclosed in a rectangular shape are the customer, the books stored in the different stores, the publishers of these books, the processes of e-commerce and the shipping details used for orders placed under e-commerce. The actions taken by the various entities are represented in the diamond-shaped figures. For example, a customer makes a purchase order for a particular book while the publisher supplies the various books available.

The attributes and details of these entities are represented in the circular shapes. For example, Books’R’Us bookstore collects various customer details such as their names, phone number, address and email address. These features would assist in keeping track of frequent and loyal customers to reward them at the end of the period. They would also assist in keeping the customer well informed about the publications available at any given time hence improving their sales revenue.

A relational model

Customer

Name Phone number National Identification number Payment method

Purchase order

Date of purchase

Mode of purchase

Book ordered

Mode of delivery

Ecommerce

Platform

Book

Name Genre Author Date of publication Date of delivery Number of units Price per unit

Publisher

Name Phone number Email address Address Date of delivery

The relationship model above shows the interrelations between different entities in the book

Relationship of these attributes with other entities. For example, the model shows the characteristics of the customer such as their name, phone number, national identification number and address as well as email. These details would help keep track of the number and type of customers visiting the store. The customer then makes orders for various books using different means such as e-commerce or walk in customers. The database would also keep a record of the books, their availability, the publisher details and their genre. It would help manage stock efficiently and in a more efficient manner.

A functional dependencies diagram normalized into 3NF

When compiling, the customer details table a lot of data is gathered for easy access and communication with that client. In the chart below, the customer details comprise the phone number, email address National ID, City of residence of the client as well as the state and street where he lives. Some of these details are dependent on each other. For example, the location of the residence of the client is dependent of the zip of the region. The contact information of the customer depends on his email address and phone number. As a result, a second table is formed to give new customer details.

Customer detail table

Customer name

Customer email address

Customer phone number

National identification

Street

City

State

Zip code

New customer detail table

Customer name

Contact details

National ID

Address

Contact Table

Contact details

Email address

Phone number

Address table

Address

City

State

Street

Zip code

The same case applies for the book inventory details. A functional dependency diagram normalized in 3NF reduces data repetition.

Books details

Book title

Author

Date of publication

Publisher

Genre

New Book table

Book Title

Publication details

Genre

Publication details

Publisher

Author

Date of publication

A functional relational schema normalized into 3NF

Customer                                          Order                                Books

Customer name                                                                                      Book Title                 Author

                                         Email address                                         Publication details      Publisher

Contact details                 phone number                                                                              Date of

                                                                                                                 Genre              Publication

Address                                 State

                                              City

                                              Street

                                              Zip code                                          Publisher

Name                                                                                                                                    Number 

Ecommerce                                  Order                                                   Contact details        Email

                                                          Shipment details

The diagram above shows a relational schema normalized into 3NF. It reduces the congestion of attributes under a single entity and spread the data evenly. It also makes the figure easy to understand as it gives a clear breakdown of the database.

A list of proposed queries

To increase efficiency in the stores, The Smiths brought forward a list of questions that would help enhance their productivity. These issues were concerned with factors that contributed to the largest number of sales by the stores. Tom and Sarah wanted to be informed of the fastest selling books and the responsible authors of these books. They also wanted to know which publisher provided these books since they would be able to minimize their cost of purchase by obtaining a discount from these publishers. Knowledge of the number of books sold monthly would also help Tom and Sarah plan ahead to avoid instances of lack of inventory. They also enquired for the addresses of the clients who purchased their books to find out which states contributed to the largest percentage of their sales. It would help the Smiths in adopting the most efficient marketing strategies. They were also interested in finding out which customer details to retain for those dealing with e-commerce. These set of questions were vital for the Smiths hence a single query that would answer these issues all at once needed to be devised. After deep consideration the following question seemed fit to ask all the concerns; what are the monthly sales of the bookstore based on customer and book details? The search result would show the sales turnover of the bookstore with all the required details.

 

"Looking for a Similar Assignment? Order now and Get 10% Discount! Use Code "Newclient"