Kudler Fine Foods Database Size example essay topic

Team C - Group Assignment Week 2 Kudler Fine Foods Proposal Instructor: POS/410 Background and Business Application Proposal Kudler Fine Foods is an upscale specialty food store, servicing the fine food cones ouer from their three California locations. Kudler's business objective is to maximize profits by offering the highest quality products at competitive prices. Through adding the on-line ordering system, Kudler hopes to boost profits by increasing their customer base beyond customers in their local areas. The Kudler on-line ordering system will add three important advantages to Kudler customers. First, the on-line ordering system will allow current Kudler customers to access the system, place their order, and have it ready for pickup when they arrive at the store. The second benefit for Kudler's on-line clientele is the added convenience of local delivery service.

This feature, allows the customer to place their order and select a time frame in which the order will be delivered to their home or place of business. The third and final new offering from Kudler will be worldwide shipping. Using major shipping companies, such as Federal Express, UPS and DHL, Kudler will provide next day shipping to customers from around the world. Database Revisions to support Application Proposal In order to implement these new services, the current Kudler database needs to be examined and revised. The current database is in a Microsoft Access format and provides a basic inventory system, as well as a list of current customers. For the new application to be a success, there are several items that need to be addressed with the current Kudler Database. o Additional Tables Needed 1.

'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: a. The transaction has obtained credit approval. Once this condition is met, the 'reservation' entry is removed. b. The items have been reserved for 30 minutes without activity, at that time the reservation entry is removed, and the items are placed back in general inventory.

2. 'Shipping Method' Table - this table will indicate how the order will be delivered. Shipping charges will be calculated by multiplying the total purchase price by a shipping factor. Options for this table will include a. In store purchase. Pickup c.

Local Delivered. FedEx e. Ups. DHL 3. 'City State Zip' table - This table will allow the customer to enter their Zip Code and the city and state will automatically populate in the online order form. 4.

Emergency Contact information - This information should be moved to its own table, making this information easier to maintain and update. 5. Building Information - Floor Space, Lease Owner, and Lease Rate should be in its own table. 6.

Unit of Measure - Should be in its own table with an ID and a foreign key added to the Inventory table. In addition to adding several tables to service the new capabilities for Kudler, there are several issues that will need to be addressed with the current database configuration. Primary Keys The first item to be addressed is that there are numerous tables that contain composite primary keys. Utilizing proper primary key and foreign key relationships will eliminate the need for composite keys. 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.

Table Changes The Store Code is currently duplicated in numerous tables. The Store code should be removed from the Tender Table, since it can be determined what store the Order was placed at by looking at the Order table which is linked to the Ordered. 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. Normalization & Relationships The objective is to normalize the database to the third normal form (3 NF). 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.

Referential integrity will be controlled through the use of primary and foreign keys. This will also make it easier to ensure information between two tables which depend on each other is not compromised. In addition to the schema changes outlined above, the Kudler database project will also include an upgrade from the current Microsoft Access Database Management System (DBMS) to a more robust version Microsoft SQL Server 2000. 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 Access, Kudler Fine Foods' database size is limited to two gigabytes (minus the space needed for system objects) Up sizing the current database to SQL Server will allow the database size to grow up to 1,048,516 Terabytes. Microsoft Access clearly will not support operations that require large amounts of data to be stored and processed.

Choosing SQL Server as a back-end also widens Kudler Fine Foods' choice of a front-end application beyond the forms and reports included with Microsoft Access.