Improving Existing Database

Improving Existing Database

Corporate management does not utilize the company database in decision support, such as forecasting. The database contains all the information necessary to forecast future needs. Some enhancements will be necessary to insure speed and accuracy, along with integrity of the data. Normalization, relationships, indexes, and changes to the tables are some of the changes. Currently the database is missing a salesperson, and item type tables. The salesperson table is needed to store data about each salesperson, so corporate management can utilize salesperson information for forecasting. The salesperson table will need a salesperson_id, salesperson_name, along with demographic fields for each sales person. The item type table can be used to forecast the product lines or types of items sold. Such as bakery, canned goods and so on. The item_type table will need item_type, and product_line fields.

The order table, along with the item table contains redundant data. "Normalization is a process for evaluating and correcting table structures to minimize data redundancies, thereby helping to eliminate data anomalies" (Coronel, Rob, 2004, pg. 184). Even though some tables have redundant data, forecasting can be done with this redundant data, with ease. These redundancies should be addressed, but not for the forecasting model. "A relationship is the association between entities" (Pratt, 2003, p. 37). Looking into the relationships of the tables will help with the flow of data. The relationships in Coporate Fine Foods database are basic, but work. The salesperson and item type tables need to be added to the relationships. The item_type field of the item_type table should be set as primary key and linked to the foreign key in the item table. The salesperson_id of the salesperson table, should be set as the primary key needs to be linked to the foreign key on the order table.

Adding or removing indexes will help with the speed and accuracy of the information retrieved for the forecasting model (Coronel, Rob, 2004, pg. 123). Such as adding an index to the item table at the item_type field, will make generating a product line forecasting report faster. With these changes corporate management can use the data stored in tables to retrieve and view data to process more accurate forecasting models.

SQL

Coporate Fine Foods needs to forecast which products to have on hand at a specific time especially during the holidays. By pulling sales information, along with item, and inventory data on a weekly, monthly, quarterly, and yearly, a forecasting model will be most effective.

Using Microsoft Access and creating queries, and reports will better prepare Kathy Coporate and her management team to be prepared for future needs of their clientele. The queries can be used to retrieve and analyze data.

Good forecasting models will not only give quantities sold at a given time, but will also break it down by store. The forecasting model will give examples of which items to have more or less on hand. Some items will not sell as well during different times, especially during holidays, or different seasons. Know which items to decrease in inventory, such as items that are not cost effective, along with which items to increase in inventory, will help Coporate Fine Foods operate more efficiently.

Coporate Fine Foods needs an easy way to generate reports, along with changing reports as needed. Microsoft Access is a good front end or user interface with the database, to accomplish this goal. Adding a form, queries, and reports any beginning users can use a forecasting model.

A forecasting model requires retrieving data to form information. Kathy Coporate and her managers need to know which products sell at a given time. Therefore, they need a report to show the quantity sold, the price and the cost, which is the quantity multiplied by the price, grouped by month. To do this, the forecasting model would use a SELECT statement. Such as, SELECT Order.Store_Code, salesperson.salesperson_name, Order.Pickup_Time, Sum(Order_Line.Price) AS SumOfPrice, Sum(Order_Line.Units_Purchased) AS SumOfUnits_Purchased, Item.[Short Description], Sum([units_purchased]*[price]) AS cost

FROM salesperson INNER JOIN ((Order_Line INNER JOIN Item ON Order_Line.Item_ID = Item.Item_ID) INNER JOIN [Order] ON Order_Line.Order_ID = Order.Order_ID) ON salesperson.salesperson_id = Order.Sales_Person_ID

GROUP BY Order.Store_Code, salesperson.salesperson_name, Order.Pickup_Time, Item.[Short Description]

HAVING (((Order.Pickup_Time) Between [forms]![mainmenu]![text1] And [forms]![mainmenu]![text2])). This SELECT statement joins the tables Order, Order_line, Item, and Salesperson, to combine all data within the tables. Using primary keys with foreign keys