ER Diagram represents only the one part of your business that deals with operations. As such...

  
 
 

Transcribed Image Text:

ER Diagram represents only the one part of your business that deals with operations. As such you will not be including any details or data regarding employees or data on the actual suppliers. You will need to track the relationships between these data collections. A BAKED GOOD, once baked will be listed in the current INVENTORY. Each BAKED GOOD will have a recipe comprised of INGREDIENTS. These INGREDIENTS will be supplied by SUPPLIER. Some EMPLOYEES of your company will manage the INGREDIENT that you use. This means they are responsible for maintaining stock of these ingredients. Finally, you will designate a specific EMPLOYEE to supervise a specific BAKED GOOD. You need to track the following: (note: the values in blue are just examples of actual data that might be used in each attribute. They are listed so the students can get an idea of what kind of data will be placed in the actual database once crated in the next assignment. They are included so the student knows how the attribute(s) will be used. For the BAKED GOOD, your design must be able to store: the Baked Good's name (i.e. Maggs Cinnamon Buns, Twisty's Croissants, etc.) * not guaranteed to be unique (i.e. A donut and a parfait could be named Spice Delight, etc.). the Baked Good's classification (i.e. cake, donut, cookie, etc.) the Baked Good storage classification (refrigerate, freezer, open air or cooler). ● ● ● the Baked Good production (true - still being made -or- false - no longer baked i.e. legacy) the Baked Good creation date (date this product was first introduced) the Baked Good discontinuation date (if product no longer baked, then date item was stopped). For the INGREDIENTS your design must be able to store: the ingredient's name (Brand Name (i.e. King's Fine Flour) - not guaranteed to be unique) the ingredient's classification (flour, milk, egg, flavoring, filling, etc.). the ingredient's quantity type (how is the quantity in hand value stored as i.e. litter, grams, etc). the ingredient's quantity (How much of the ingredient in on hand i.e. for eggs it would 3 units). ● ● For the INVENTORY (information on each baked good currently on the selves), your design must store: ● ● For the SUPPLIER (information on each company that supplies the ingredients used in your bakery), your design must store: the supplier's name (i.e Pillsbury -or- Gold Seal, etc.). the supplier's status (true – still using as a supplier -or- false - no longer buying from them) ● For the EMPLOYEE, your design must be able to store: the employee's last name (i.e. Smith, Jones, Huang, Nasser, etc.) the employee's first name (i.e. John, Jane, Anisha, Yunfei, etc.) the employee's Date of Hire (August 1, 2020 or July 6, 1955). All attributes are to be considered as mandatory unless their use can be logically left empty. ● the quantity on hand (the number of items on the self currently for sale). the best before date (date the item should be sold by: August 12, 2023, etc.) the price per unit for this baked good (price in dollars). ● ● Next, you need to store the following relationships: When BAKED GOOD is made, it is listed (added to) the INVENTORY. A BAKED GOOD may be listed only once in the INVENTORY. note: when an item is baked (i.e. a Coconut Cake is made), then the Quantity value will be increased by 1 for this item in the INVENTORY. A new INVENTORY item is NOT added the INVENTORY unless it has not previously been listed in the INVENTORY. ● ● (So, if you already had a listing for Coconut Cake and you baked another one today, then you would increase the quantity of Coconut Cakes by 1. You would not add a listing for a Coconut Cake each time. But if tomorrow you bake a Chocolate Cake and this is the first time your bakery has ever made a Chocolate Cake, you will need to add a new listing to the BAKED GOOD entity and call this listing 'Chocolate Cake'. This is exactly the same as if you were keeping count of INVENTORY using a paper and pencil. Every time you baked something, you would look at your list and if the item was already on the piece of paper (say Coconut Cake) then you would just increase the quantity on hand by 1. If looking at your list you did not have a listing for the item you just baked (say Chocolate Cake), then you write that item down on your paper and list the quantity on hand as 1. The idea of creating a database entity to accomplish this task is to replace the paper and pencil tracking with a computer database. When you sell an item (say a Coconut Cake) then you would decrease the quantity by 1 The goal here to have a current and up to date listing of what and how much your bakery has on hand to sell at any given time..) An INVENTORY entry must be associated with one existing BAKED GOOD item listed. A BAKED GOOD is made from a recipe (list of) INGREDIENTS. A BAKED GOOD must be made from (recipe) of one or more INGREDIENTS. An INGREDIENT may be used in (recipe) a BAKED GOOD. ● A SUPPLIER might supply the INGREDIENTS used in your baking. A SUPPLIER may supply one and only one INGREDIENT. ● ● note: when an ingredient is added to the recipe, the following must also be stored for each: the ingredient's quantity type (how is the quantity on hand value stored as i.e. litter, grams, etc If the item is a discrete unit (example: 3 eggs are used in the recipe) then use the quantity type of 'unit' (example UNIT)). the ingredient's quantity (How much of the ingredient is used in this specific recipe). ● note: A SUPPLIER may supply no INGRDIENTS - but is still listed. Think how this will affect any foreign keys An INGREDIENT must be supplied by one or more SUPPLIERS. note: for example: Supplier A can only supply only one ingredient (say Flour) Supplier A cannot supply any other ingredients to your bakery Supplier B also can only supply one ingredient, but that ingredient could also be Flour. An EMPLOYEE will manage the INGREDIENTS used in your baking. An EMPLOYEE may manage one or more INGREDIENTS. An INGREDIENT must be managed by one and only one EMPLOYEE. An EMPLOYEE might not be assigned to manage any INGREDIENT. note: - when the EMPLOYEE is assigned to manage an INGREDIENT, the date the EMPLOYEE was assigned must also be stored. REMEMBER: An E-R Diagram is a blueprint for a database you will build in the next assignment. As such, there is NEVER any actual data in your diagram. For example, if your BAKED GOOD entity has an attribute name like 'Bow Ties', then it is incorrect. 'Bow Ties' would be an example of actual data you enter into the database created from this E-R Diagram. The entity must only contain the labels you will use to store the data in the database you will be creating. A possible label for the pastry name attribute in your BAKED GOOD entity could be: Baked GoodName (no spaces), or Baked Good Name, or Pastry, or any label you think is descriptive enough for the data that will eventually be placed in that field in the BAKED GOOD table in the database you will create in the actual database. This applies to each and every label in each and every entity in your diagram. Your diagram must be correct and meet the specifications given above. Hint: There are exactly six (6) entities in the completed ER diagram. No more and no less will be acceptable. You will create your answer to this project using the graphic design tool Dia.exe. You MUST follow all the standards and formats in the class notes Arrow Size of all your Lines MUST be exactly 0.80 (makes the lines easier to read). ER Diagram represents only the one part of your business that deals with operations. As such you will not be including any details or data regarding employees or data on the actual suppliers. You will need to track the relationships between these data collections. A BAKED GOOD, once baked will be listed in the current INVENTORY. Each BAKED GOOD will have a recipe comprised of INGREDIENTS. These INGREDIENTS will be supplied by SUPPLIER. Some EMPLOYEES of your company will manage the INGREDIENT that you use. This means they are responsible for maintaining stock of these ingredients. Finally, you will designate a specific EMPLOYEE to supervise a specific BAKED GOOD. You need to track the following: (note: the values in blue are just examples of actual data that might be used in each attribute. They are listed so the students can get an idea of what kind of data will be placed in the actual database once crated in the next assignment. They are included so the student knows how the attribute(s) will be used. For the BAKED GOOD, your design must be able to store: the Baked Good's name (i.e. Maggs Cinnamon Buns, Twisty's Croissants, etc.) * not guaranteed to be unique (i.e. A donut and a parfait could be named Spice Delight, etc.). the Baked Good's classification (i.e. cake, donut, cookie, etc.) the Baked Good storage classification (refrigerate, freezer, open air or cooler). ● ● ● the Baked Good production (true - still being made -or- false - no longer baked i.e. legacy) the Baked Good creation date (date this product was first introduced) the Baked Good discontinuation date (if product no longer baked, then date item was stopped). For the INGREDIENTS your design must be able to store: the ingredient's name (Brand Name (i.e. King's Fine Flour) - not guaranteed to be unique) the ingredient's classification (flour, milk, egg, flavoring, filling, etc.). the ingredient's quantity type (how is the quantity in hand value stored as i.e. litter, grams, etc). the ingredient's quantity (How much of the ingredient in on hand i.e. for eggs it would 3 units). ● ● For the INVENTORY (information on each baked good currently on the selves), your design must store: ● ● For the SUPPLIER (information on each company that supplies the ingredients used in your bakery), your design must store: the supplier's name (i.e Pillsbury -or- Gold Seal, etc.). the supplier's status (true – still using as a supplier -or- false - no longer buying from them) ● For the EMPLOYEE, your design must be able to store: the employee's last name (i.e. Smith, Jones, Huang, Nasser, etc.) the employee's first name (i.e. John, Jane, Anisha, Yunfei, etc.) the employee's Date of Hire (August 1, 2020 or July 6, 1955). All attributes are to be considered as mandatory unless their use can be logically left empty. ● the quantity on hand (the number of items on the self currently for sale). the best before date (date the item should be sold by: August 12, 2023, etc.) the price per unit for this baked good (price in dollars). ● ● Next, you need to store the following relationships: When BAKED GOOD is made, it is listed (added to) the INVENTORY. A BAKED GOOD may be listed only once in the INVENTORY. note: when an item is baked (i.e. a Coconut Cake is made), then the Quantity value will be increased by 1 for this item in the INVENTORY. A new INVENTORY item is NOT added the INVENTORY unless it has not previously been listed in the INVENTORY. ● ● (So, if you already had a listing for Coconut Cake and you baked another one today, then you would increase the quantity of Coconut Cakes by 1. You would not add a listing for a Coconut Cake each time. But if tomorrow you bake a Chocolate Cake and this is the first time your bakery has ever made a Chocolate Cake, you will need to add a new listing to the BAKED GOOD entity and call this listing 'Chocolate Cake'. This is exactly the same as if you were keeping count of INVENTORY using a paper and pencil. Every time you baked something, you would look at your list and if the item was already on the piece of paper (say Coconut Cake) then you would just increase the quantity on hand by 1. If looking at your list you did not have a listing for the item you just baked (say Chocolate Cake), then you write that item down on your paper and list the quantity on hand as 1. The idea of creating a database entity to accomplish this task is to replace the paper and pencil tracking with a computer database. When you sell an item (say a Coconut Cake) then you would decrease the quantity by 1 The goal here to have a current and up to date listing of what and how much your bakery has on hand to sell at any given time..) An INVENTORY entry must be associated with one existing BAKED GOOD item listed. A BAKED GOOD is made from a recipe (list of) INGREDIENTS. A BAKED GOOD must be made from (recipe) of one or more INGREDIENTS. An INGREDIENT may be used in (recipe) a BAKED GOOD. ● A SUPPLIER might supply the INGREDIENTS used in your baking. A SUPPLIER may supply one and only one INGREDIENT. ● ● note: when an ingredient is added to the recipe, the following must also be stored for each: the ingredient's quantity type (how is the quantity on hand value stored as i.e. litter, grams, etc If the item is a discrete unit (example: 3 eggs are used in the recipe) then use the quantity type of 'unit' (example UNIT)). the ingredient's quantity (How much of the ingredient is used in this specific recipe). ● note: A SUPPLIER may supply no INGRDIENTS - but is still listed. Think how this will affect any foreign keys An INGREDIENT must be supplied by one or more SUPPLIERS. note: for example: Supplier A can only supply only one ingredient (say Flour) Supplier A cannot supply any other ingredients to your bakery Supplier B also can only supply one ingredient, but that ingredient could also be Flour. An EMPLOYEE will manage the INGREDIENTS used in your baking. An EMPLOYEE may manage one or more INGREDIENTS. An INGREDIENT must be managed by one and only one EMPLOYEE. An EMPLOYEE might not be assigned to manage any INGREDIENT. note: - when the EMPLOYEE is assigned to manage an INGREDIENT, the date the EMPLOYEE was assigned must also be stored. REMEMBER: An E-R Diagram is a blueprint for a database you will build in the next assignment. As such, there is NEVER any actual data in your diagram. For example, if your BAKED GOOD entity has an attribute name like 'Bow Ties', then it is incorrect. 'Bow Ties' would be an example of actual data you enter into the database created from this E-R Diagram. The entity must only contain the labels you will use to store the data in the database you will be creating. A possible label for the pastry name attribute in your BAKED GOOD entity could be: Baked GoodName (no spaces), or Baked Good Name, or Pastry, or any label you think is descriptive enough for the data that will eventually be placed in that field in the BAKED GOOD table in the database you will create in the actual database. This applies to each and every label in each and every entity in your diagram. Your diagram must be correct and meet the specifications given above. Hint: There are exactly six (6) entities in the completed ER diagram. No more and no less will be acceptable. You will create your answer to this project using the graphic design tool Dia.exe. You MUST follow all the standards and formats in the class notes Arrow Size of all your Lines MUST be exactly 0.80 (makes the lines easier to read).

Answer rating: 100% (QA)

Entities Baked Good Attributes BakedGoodName Classification StorageClassification Production CreationDate DiscontinuationDate Relationships Onetomany ... View the full answer