The company Kudler Fine Foods supplies the freshest ingredients and the tools enabling their customers to become a gourmet cook. They are a specialized food store with a worldwide selection of vegetables, meats, wines, and cheeses. The stores are owned by Kathy Kudler, and today continue to grow with high expectations offering their customers the convenience of a one-stop shopping experience. Ms. Kudler opened her first store in La Jolla, and after a five-year plan, opened her third store in Encinitas. She is committed to providing her customers with the best selection of fine foods and great wines.
Evaluation of the design elements The Inventory Table contains the components that make up an Item. It is used for managing inventory and determining the availability of ingredients that go into prepared items, such as bakery products, etc. First, the table organizes the financial data into department, then financial account codes, item, and then sums the total amount of transactions for the code and then counts how many quantities of the items were in inventory during the period. Second, I think that this design maximizes the visual and informational presentation for the financial data.
Given this design, the user of the information can readily identify what items belong to a certain department, and how many items were in to arrive at the total cost for a particular item for the period. Third, given the presentation of the total amount and quantity on hand for an item, the information user can easily calculate the effective average purchases price for an item. . Entity Relationship Diagram The following diagram (1-1) is entity relationship (E-R) diagram and it illustrates the existing data tables.
Recommend improvements to the data tables The data tables, specifically the Pivot table, may be improved on by arranging the codes chronologically. This would facilitate easy monitoring of items particularly for a department, which has a lot of items to sell. In order to implement any new service, the current Kudler database needs to be examined and revised. The current database provides a basic inventory system. For the new application to be a success, there are several items that need to be addressed with the current Kudler Database.
For instance, Kudler needs additional tables such as Reserved Inventory table. This table will be a transaction table that will reserve the items by removing them from inventory and holding them in the reserved table until one of the following conditions is met. In addition, the primary keys that are preserved will need to be configured as required fields, and there are several primary keys that can be replaced with Auto-Number Identities. The Inventory table will also need to be modified to indicate what quantities are on hand at each individual store.
Under the current database, there is no visible way to show which store has inventory. For example, one store may have 35 boxes of Frog Legs, while there may be two other stores that are out of stock. Normalizing the database will ultimately result in minimizing data redundancy and optimizing the data structures by containing smaller, more flexible tables. Once the tables are normalized and data integrity has been maximized, referential integrity will be addressed by establishing relationships between data elements and their appropriate groupings.
Upgrading to SQL Server is a more viable alternative should Kudler Fine Foods’ require a larger database capable of faster operations due to business growth and the collection of historical data. Using Microsoft Office applications, Kudler Fine Foods’ database size is limited to two gigabytes (minus the space needed for system objects). Upsizing the current database to SQL Server will allow the database size to grow up to 1,048,516 Terabytes. Pivot Table in Microsoft Excel using the general ledger inventory data This pivot table will be used to provide more information to management at Kudler Fine Foods.
The Inventory Report as of 12/31/2003 provides limited information. If I had more details, I would design the Pivot table as follows: Item#, Description, Unit Cost, Sales Price, Beginning Quantity on Hand, Beginning Quantity on Hand, Date, Category, Department, Supplier ID, and Inventory ID. (Please refer to Microsoft excel attached file to view my pivot table in both sheet 1 and sheet 2. ) The pivot table is two-dimensional statistical summaries of database information a two-dimensional analysis of the inventory Pivot tables enable users to choose what type of summary information to display.
The pivot table enables managers to choose what type of summary information to display as well as to change an overall selection category. For example, A specific item (Agiago Cheese) in La Jolla Bakery Department Merchandise Inventory (GL code: 121012) the sum of amount is 1061. 63. This number is a meaningful number for the management. The managers can arrange information, compare, reveal and analyze by displaying different views of data, turning data that make sense, it will improve decision making for management at Kudler Fine Foods.