solve this in 3rd normal form and also draw an erd diagram for this

Draw an Entity Relationship Diagram (ERD) for this situation below in 3rd normal form.

SITUATION:

You own a confectionary store called Alberta Sweetness. You started with one small store, and due to your

success, you are now opening three new locations. Youve been managing a bit by feel in the original store,

but you know you will not be able to keep as close of tabs on the four stores and will need some data to make

decisions.

You will need to maintain a list of all confectionary products carried in your stores. For each store, you will need

to maintain the inventory of all products. For each product, you track a list price and cost price, but for now, you

require the prices to be consistent across all stores. You dream of someday opening stores across Canada and

know the price will vary across Canada, but for now, you want to focus on the minimum viable product and build

the necessary scalability into the database. You do anticipate opening more stores throughout Alberta.

You also have an old customer database where you track customers. Their purchases are tracked via invoices,

including the quantity, list price, percentage discount, and price paid for each item. At the time of sale, the list

price matches the list price for the product, but the product list price can change, whereas the invoice list price

remains the same. Unfortunately, this database has been corrupted and you need to start from the beginning.

Products are classified into different categories (such as gum, gummy, lollipop, licorice, etc.). These categories

are further grouped into product lines (candy and chocolate for now, although you anticipate adding ice cream

in the future). Each product may belong to only one category. Additionally, products are associated with

specific tags to help identify sales opportunities. For instance, candy hearts may be associated with the tags

anniversary, romantic and Valentines.

ADDITIONAL INFORMATION:

While not all products are available at all stores, there is a large overlap in the products.

In addition to the invoice percentage discount for each item, there may be an overall discount applied to a

complete invoice.

For each customer, an address and list of phone numbers are tracked. Previously you only allowed customers to

enter a home and mobile number, but you found many customers are giving an alternate mobile number

and you have decided the new database should support as many phone numbers as customers choose to give.

At least one phone number is required for easy customer lookup.

For small quick purchases, without a discount, there is a guest customer that is associated with the

purchase. This customer is associated with the phone number 000-000-0000.

Stores are identified by a short name (e.g. Kensington) and a full name (e.g. Calgary Kensington). It is

important to understand what products are sold in which stores.

Although somewhat redundant based on the list price and percentage discount, the actual sell price and total

invoice amount are both stored in the database for easy querying later.

Products are sold either by weight, item, or package. You cant be sure that you wont allow additional methods

(such as length or alternate weights) in the future, so you have decided to allow for new methods to be added.

Assume there is no sales tax on any of the items.

REQUIREMENTS:

Create an Entity Relationship Diagram (ERD) for your Alberta Sweetness situation. Include the cardinality of

relationships and make sure to define the primary (PK) keys(s), foreign (FK) key(s), composite keys (FK,FK), and

which attributes are involved in each relationship.

All bridging tables should make use of a composite key. Many-to-Many relationships may not be used, and your

final product should be in 3rd normal form

SITUATION: You owh a coefectionary sore caled Atberte Sweetness. You srited whi sne small soine, and the no your tuccess, you are fow opening thrite new locatoss youle beps managing a be by ther in the or ignal seore. but you know you wit not be alle bo keep a dese of tabe on the fout stores and will need woeie doa to make dechisent. You wil need to maintain a lint of al cooknctioniry products carried in your stores. for pach store vou will need to maintain the ineentery of all prodects. Fer each peoduct, rey back a bet price ind ent price, bst fer nom, you Fequire the prices to be corsolent acters all sworei. Fou diedn of renetay opening stores acoss Carude amd krow the price wil vary acrocs Canadh, lut for now, pou mant to focus on the minimum veble product and buld the necestary scababilty ints the dotabase. Fou do articieate optinit more stores throuphont Aberta. including the quartate Gyt price, pescertage diccount, and price paid for ewch itum, At the time of wie, the ast grice matches the lit price for the grosuct, but the product but prise can chunge, whereas the impoise hat price renains the ame. Unfortunatele, this databse has been coercoted and vou ned to start from the begining. Products are clasiled ints diterent categories fsechas gut, gammy, lellpop, toonce, esc., these canegoriet are further grouped inte peoduct lines frandy and thocplute for now, alhough you anticipate adding ice cream in the futurel. Cach product mar belons to oely one atesory. Aditicully. sroducts are assocuied with ipecife tass to help idettif sales esperturitien. Far inifecce, candy harts may le anseciated with the tass "inniverare". "timantec" and VWientines". ADDITIONAL INFORMATION: While not alf products are avilyble at all seeres, there in a laree everlag in the products. camplute imaice. for each cuitomev, an addivix and lat ef phene members are tracked. Freviously yeu only ablewed putomers bo emier a home asd mobile number, but you fosnd many owamomen ate glving an aheraibe mobile tamber and vou hive decided the new ducabane theald support as many phene numberi as cuntomert cheove bo iove. Al least ent ahone number in reguiece for tavy cuitomer leokip. for imall quick purchases, witheut a daceunt, there is a "gucit" cuilgemer thyt in asdociatid with the guethase. The cuntomer is asupcuted with the rhone number "opo-0od-obed. Steres are identitied by a short name fe t. Keroingtons and a ful name fe t. Caleary-keningtond it is imoortant oo sndertibnd what products are sold in ahich steres. Aithough iomewhat iedundant buned po the liat prite and perventage discount, the actual sedt price and toeal invoke anoset are both stosed in the datalase for eavy auenving later. Products are sold either ly weidr, itern, of packes. You can't be wore that you won't dloas additienal methods fwah at lesgth or alvemate weig.tif in the Hutuie, to you have decided to allow for new mathedis to te added. Maswme there is nos sales tax on any af the Remo. REQUIREMENTS: which astititutes are imotved in each selatienthip. Al toridgine tabies should make use of a composice key. Mary to Many redabenchios may not be uned, and pour final preduct sheuld be in 3 ? novnof form. ADDIIIONAL REQUIREMENT AND NOTES: (i). This is groug insigneit, that in, eadh poup iheuld complete the asignmest os their own. Reler to not be tolerned. (2) pleaie submit your the in Poe foemat gaviy, Name your file accondine to your aroup fumber. It you ate in group 1, lectire 1, please name your por bine "gsupgilectureots par" 3. Submit your Pof file to the coniect Dropber in oas betore the asignment 2 due date. Late subminsions wil recelve a crade of reso as per the late policy within the ceurse cutline. b. Do not emal your asvignnem to your inatructor. Submisisms va ental will not be accepeed