Kudler Fine Foods Database

The Company

The company is a store name Kudler Fine Foods, supplying 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 cheese, to mention a few. The stores are own by Kath Kudler, became a reality in 1998, and today continues to grow with high expectations offering their customers the convenience of a one-stop shopping experience. Ms. Kudler opens her first store in La Jolla, and after a five-year plan, opens her third store in Encinitas. She is committed to providing her customers with the very best selection of fine foods and great wines.

The Project

The project is that of a Microsoft 2000 Access database representing all the associated Kudler Fine Foods stores. The current database utilizes tables that are in reference to customers and the organization operations. Many of the tables require normalization, creating new tables, Entity Relational Model (ERM) relationships, and investigating the requirements of associated indexes. New reports are required in support of the relationships between tables, business operations, and the necessities in meeting the need of the customer.

The current tables are as follows:

Table Description

Customer The Customer Table contains demographic data for each of Kudler's customers. The data in this table is used to access the name and address of customers for order processing and for special mailings for anniversaries and/or birth dates, etc.

Inventory 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.

Item The Item Table stores information pertaining to products that may appear on an order. It describes goods that can be purchased from a Store.

Order The Order Table is used to record information pertinent to each Order placed with a given store. Each order will have one or more entries in the Order Line Table associated with it.

Order Line The Order Line table contains detail on items appearing on an order. One or more Order Lines are associated with each order and each Order Line references an item being purchased with its description, quantity, price, etc.

Store The Store Table retains data for each store location, such as name, address, phone numbers, emergency contacts, etc.

Supplier The Supplier Table contains data concerning wholesalers, vendors and other suppliers from whom inventory is procured. These goods are used to prepare saleable items or are simply resold in their acquired form.

Tax Table The purpose of the Tax Table is to provide tax rates applicable to orders placed at a given store.

Tender The Tender Table provides a means to record the methods of payment used for an Order. For an example, an order may be paid for partially in cash and partially by credit card.

The current Kudler Fine Foods Entity Relationship Model (ERM) diagram:

Data Modeling

The current Kudler data model permitted our team to capture and understand the present flow of information. The ERM diagram, developed as part of the data modeling process, was used as a communication media with each other to analysis the business operations. The focus of the ERM model is to capture the relationships between various entities of the organization or process for which we design the model. The ERM diagram is a tool that our team used to assist in analyzing the business requirements and in the design of the resulting data structure.

The current Kudler Fine Foods Entity Relationship Model (ERM) diagram:

Indeterminate Relationships

Generally, a model is that of an abstraction and reflection of the real world in reference to Kudler Fine Foods operations. In creating tables, modeling gives us the capacity to visualize what we, up until now, could not realize. It is the same with data modeling. The primary plan of our data model is to make sure that all the data objects accurately represent the database during the normalization. The data model will easily verify and decipher primary keys, foreign keys, and tables used in the new design collectively with the current tables in building the data structure and the verification of the appointed index.

Our objective of normalization is to reduce redundancy by not containing the same data stored in numerous tables. As a result, normalization can minimize any integrity issues because SQL permits us to update a single table when required. However, queries, particularly those involving larger tables, which include a join of the data stored in multiple normalized tables, this may require additional effort to achieve an acceptable performance to the business operations.

Although data in normalized tables minimizes redundancy, it became a challenge for us to navigate. Our team believes if an analyst must navigate a data model that requires a join of 15 tables; it may likely be difficult and not very intuitive. As a team, we strongly recommend third normal form for OLTP applications since data integrity requirements are stringent, and joins involving large numbers of rows are minimal.

Our ERM focuses on three major contributions, entities, attributes, and relationships. An entity is any category of an object in which the Kudler Fine Foods is interested. Each entity has a corresponding business definition to define the boundaries of the entity allowing our team to decide whether a particular object belongs to that assigned category or entity.

Identify the requirements:

We focused on both the ERM and dimensional model of the business giving us an improved capability to visualize the abstract questions concerning the business functionality.

The Dimensional Model

The required tables in the normalization phase:

Identified

Dimension

Granularity of the dimension

Related Source Tables

...