4-2 Final Project Milestone Two: Systems Analysis Jason Wedge Southern New Hampshire University IT-330-X5595 Database Design and Management 16EW5 For

4-2 Final Project Milestone Two: Systems Analysis

Jason Wedge

Southern New Hampshire University

IT-330-X5595 Database Design and Management 16EW5

            For this project, we will design and create a database for Book ‘R’ Us, a small privately owned bookstore. The store currently relies on a Microsoft Excel spreadsheet. This method was suitable when there was only one store. Now that the owners have acquired a second store, Great Books USA, a more efficient method would need to be put in place. Creating an Access database will assist the owners of the stores in tracking inventory and customers for both stores in a more efficient and centralized manner.

This database would allow the owners to track buying trends, inventory and also keep track of customer’s order history. This would help the owners when book publishers offer discounts on a certain quantity of books ordered.

The alternative to this would be to continue relying on Microsoft Excel which does not have the features of an Access database. While it is relatively easy to create formulas, reference cells, copy and paste data, and link worksheets and spreadsheets together, as the work gets more complex, spreadsheets become more difficult to edit and manage. While spreadsheets are ideal for creating one-time analysis, they become problematic as the data grows and evolves over time. As new rows and columns get added, summary ranges and formulas may need to be modified or new ones created, data and formulas aren’t consistently updated, and these mistakes lead to bad results and decisions.

The new database will need to combine two independent bookkeeping and inventory systems into one master system that will provide the owners with accurate data that will help their business grow. They would like to keep track of data points for both marketing and financial reasons. Building a SQL table will allow them to cross reference tables to get the information that is needed for their marketing efforts. This would also help them in new marketing ideas or changes in inventory that could arise in the future.

This database will include 8 tables containing multiple attributes. The first table with be BOOKSTORE with the attributes of store address and id number (primary key). This will help separate store inventory and sales. The next table will be INVNETORY TYPE. Where the stores sell more than books it will be necessary to show what is being sold. The attributes will be Item category code (primary key), store id number (foreign key) and quantity in inventory. The next table is the BOOK INFORMATION table. These attributes include ISBN (primary key), a code for new or used books, author name, publisher and price. There should also be a table to keep track of the General Merchandise items that the bookstores sell. This table would reflect the Product ID number (primary key), product description and price. To keep up with sales and an upcoming internet sales campaign, we will create a table for CUSTOMER INFORMATION. This table will include customer ID number (primary key), address, phone number, email address and zip code.

There would also be an Order table. This table will identify sales information. The attributes of this table will include order ID number (primary key), store ID number (foreign key), customer ID (foreign key) customer zip code, employee ID (foreign key) and invoice ID. The last table would be for employees’. This table would show employee id (primary key), employee first name, last name, phone number, address, e-mail address, zip code and date of hire.

List of Proposed queries

1.How many books are sold monthly by the publisher? This is important due to the fact that quantity discounts are available from the publisher.

SELECT month(Date) AS month,

COUNT publisher FROM Salesdetail

GROUP BY month(Date) ORDER BY month;

2. Which authors are the most popular sellers of books in the stores? This is important because the publisher often give discounts for certain authors each month.

SELECT author,
FROM books
WHERE productid IN
(SELECT productid
FROM orderdetail
GROUP BY productid
HAVING SUM(quantity) = 
(SELECT MAX(COUNT(*))
FROM orderdetail
GROUP BY productid));

3. What books are associated with which publisher?

4. What are the most commonly sold products other than books that are sold in each store? In addition to books, the stores sell magazines, café-specific products like coffee and pastries, and a slection of gift items.

5. From what region (by ZIP code) do customers visit the stores? This will assist with future marketing endeavors.

Select Count (CustomerZipCode)

From OrderMaster

           Group by CustomerZipCode

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