Lucky Preserves

Lucky Preserves is a small shop in Mon Valley that has been in business over several years. The shop is owned by Carissa McNally, who has operated this business for over 20 years now. The store sells various types of pickles, jams, sauces, preserves, jelly and many more items. Many of these items are produced seasonally depending on the fresh produce available in the season.

For all these years, Carissa has been keeping a record of the items and its recipes, raw materials and vendors using an Excel spreadsheet. She would keep track of every batch of items manufactured, along with the quantity of items produced and the raw materials used. If she falls short of any raw materials, she would call up the particular vendor to place an order. When a shipment of raw materials arrive, they would be updated into the table of raw materials. Over the past years Carissas business has grown and she is finding it increasingly tedious to keep track of single item sold. Furthermore, she is figuring out ways to automate her raw material tracking process. One of the first things she plans to do is to create a comprehensive database for her system. Assume you work in a team and your job is to design a part of the database. Here is a brief description of information that you might need to do your part of the job:

In order to make it easy to organize items in the store, Carissa has categorized the items into various product categories such as pickles, jams, sauces, preserves, flavored oils and fruit concentrates. Each item put on sale, belongs to exactly one category. Each item put on sale has a specific item ID and item name associated with it. For example item#489 is an 8oz bottle of Barbeque sauce, item #178 is an 16 Oz bottle of Barbeque sauce and item #987 is a 4 oz bottle of orange marmalade etc.

Each item has a recipe that is used to manufacture that item. A single recipe, can be used to produce multiple items. For example, a single mixed berry jelly recipe can produce item #456 2oz jar of mixed berry jelly , or item #789 4 oz packet of mixed berry jelly.

Each recipe requires several raw materials, such as different kinds of produce, oils, vinegars, spices, wines etc, in various proportions. An example of a raw material is white sugar that can be used in multiple recipes. Each recipe provides the quantity a raw material that needs to be used in that recipe.

Each of the raw materials are supplied by a preferred vendor. Each vendor may supply multiple raw materials. For example, the local farm may supply seasonal produce, condiments such as honey, cider etc. Carissa has one preferred vendor to supply each raw material.

It is your job to create an ERD for part of the new DBMS that relates items to their category, and to their recipes, raw materials and vendors of raw materials.

Anything outside of the scope of what has been described is not part of this exam. In other words, this is very straight forward and, realistically, pretty simple. And since we havent covered anything beyond entities and their relationships, that is all thats required (no attributes, PKs or FKs yet).

We will approach this in steps.

Following describe the business this is where you will extract your business rules. Refer to the business narrative as needed.

Each item on sale belongs to exactly one category. Each category can contain multiple items. Some categories like cheeses, have been discontinued and dont have any items under it.

Each item has one recipe. Using one single recipe many items can be produced.

Each raw material can be used in one or more recipes. Each recipe mentions a raw material once. Even though the narrative (in paragraph 4) mentions that each recipe has several raw materials, how you model the table is important. You can think of the recipe table having an entry (record) for each recipe-raw material combination. So effectively, the recipe table will, on the whole, have multiple entries for each raw material. Each entry in the recipe table will have only one raw material listed in it.

A raw material is supplied by a preferred vendor and the same vendor may also supply other raw materials. Only preferred vendors who are currently supplying raw materials need to be listed in the database.

Step 1:

By reading through the business description above, list each of the entities that you plan to use in your ERD:

• Item
• Made by
• Raw Material
• Recipe
• Requirements
• Vendor
• Shipping

Add space as need to the answer

Step 2:

Write the business rules that dictate the relationship between the entities that you have listed. Now, in the same way as shown in the video your professor created for Chapter 2, clean up the business rules so that they include only each entity, the relationship and the connectivity. Write the relationship bi-directionally.

Add space as need to the answer

Step 3:

Complete the ERD in Visio. Using the snipping tool, paste your ERD below.