Data Model

Some of the data needs of Overly’s Country Christmas are that of knowing where their customers are from, how many people tend to come from each county, and where from each county they are from so they can advertise more effectively to their customers. To collect this data, Overly’s uses small cards that are handed out at the gates to be filled out by people coming in. These cards ask for the customer’s county, zip code, number of people with them, how they heard of Overly’s and a few other questions. At the end of each year the director inputs this data into an excel sheet called the “master form” which then generates graphs and tables in excel. The problem is that the current director doesn’t know how to change or really use the excel sheets to make graphs anymore because the person who set it up left a few years ago. If the data collected could be plugged into a Data model in access, relationships between different primary keys could be made so the data could be used and read easily for decision making. We made a simple example data model using the information from the provided data from Overly’s to show how a Data model such as this could be helpful in simplifying and using the data since the current excel sheets being used aren't fully usable or understood. 

The 2013 excel sheet provided to us by Overly’s was able to create this pie chart, but since the person who created this excel sheet is no longer there the current director can’t use the information gathered from more recent years to create charts like this.

 

This is the current “master form” that the data collected is put into each year.

In the example data sheet Visitors by County sheet, the key element assigned to the sheet is county. By having the key element of this sheet be County we can link the other tables, Visitors by date and Visitors by Zip code, back to this table so the correlations between County, zip code, and the amount of people that visit each month can be seen and used to make decisions about things like advertising.

For the example data sheet Visitors by Date, the primary key assigned to the sheet is people. With the key element of this form being the number of people visiting, this allows the table to be linked to the other two data sheets so the relationship between the number of people visiting, their county, and their zip code can be looked at and used to know where a majority of customers are from so something like advertising can be adjusted to gain more customers from countys with a low turnout rate.

The example data sheet Visitors by Zip code, the primary key assigned is Zip code. This allows this data sheet to be linked to the other two so the correlations between the amount of people from each county and what their specific zip code is can be used to know where a majority of their customers are from. Being able to see the relations between the data can help marketing decisions so more advertising could be done in some regions and less or the same in others so the maximum number of customers can be reached.

The last aspect of the data model created is the relationships page from the access sheet. This shows the link between the three primary keys used. By using a data model like this Overly’s could start from scratch a new way of saving and using the data collected rather than continuing to use an excel sheet no one knows how to use anymore. This data model shows the correlations between information from the customer data collected could be used more effectively and show the relationships between the data so decisions can be made based on patterns and relations seen.

General Sources used:

Class lesson on database processing

Textbook: Kroenke, D. (2014). MIS Essentials (4 edition). Boston: Prentice Hall.URL