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
Books’ R’ Us Database Proposal
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.
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.
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.
The following is a breakdown of the entities and attributes that should be included in the database design.
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
A comprehensive entity-relationship diagram (ERD)
Customer name Book title
Customer Address PublisherBook author
Street Name Year of Publication
State Phone numberPublisher
Zip code Genre
E-commerce order delivery Shipping details
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
|Name Phone number National Identification number Payment method|
|Date of purchase|
|Mode of purchase|
|Mode of delivery|
|Name Genre Author Date of publication Date of delivery Number of units Price per unit|
|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 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 details||Email address||Phone number|
The same case applies for the book inventory details. A functional dependency diagram normalized in 3NF reduces data repetition.
|Book title||Author||Date of publication||Publisher||Genre|
New Book table
|Book Title||Publication details||Genre|
|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
Zip code Publisher
Ecommerce Order Contact details Email
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.