Dessert Database Let\'s say you work for a large restaurant chain. There is a ne

Question

Dessert Database

Let's say you work for a large restaurant chain. There is a new project underway to report on the popularity of desserts within each restaurant. Management would like answers to questions, such as the following:

What are the best- and worst-selling three desserts each month?

What are the best- and worst-selling three desserts by region each month?

What are the best- and worst-selling three desserts by restaurant each month?

What are the overall best- and worst-selling desserts by restaurant each month?

What are the best- and worst-selling desserts each year?

Do the restaurant's recommended dessert toppings appear to influence the sale of desserts?

Do the restaurant's recommended drinks for each dessert appear to influence the sale of desserts?

Is there a correlation between the booth location or booth type and the quantity of desserts ordered?

Well, they came up with this database-model and now it's up to you to decide what's next!!

Is this design OK and you let them go on implementing or do you want do do more. Show your expertise.

Change the diagram as you see fit to best answer the questions above and then Normalize the diagram to 3NF

Entity:s Attributes

[Dessert] Dessert Name, Dessert Offered Date Range, Dessert Code, Drink Name, Topping Name, Dessert Category Code, Dessert Price Amount, Dessert Description

[Restaurant] Restaurant Region Name, Restaurant Identifier, Restaurant Region Code,
Restaurant Maximum Capacity Number, Restaurant Owner3 Name, Restaurant Owner 2 Name, Restaurant Owner 1 Name

[Check] Check Identifier, Granuity Amount, Check Amount, Check Date and Time

[Booth] Booth Type Text, Smoking Section Indicator, Booth Capacity, Booth Location Code,
Booth Type Code

Relations

Restaurant 1 Process M Check
Restaurant 1 Contain N Booth
Check M Generate 1 Booth
Dessert M Offer N Restaurant
Dessert 1 Contain N Check

Explanation / Answer

Some attributes may to be added to the existing database attributes to get better results. They are shown in bold.
Entity:s Attributes
[Dessert] Dessert Name, Dessert quantity, no_of_sales, Dessert Offered Date Range, Dessert Code, Drink Name, Topping Name, Dessert Category Code, Dessert Price Amount, Dessert Description

[Restaurant] Restaurant Region Name, Recommended drink, Recommended Topping, Restaurant Identifier, Restaurant Region Code,
Restaurant Maximum Capacity Number, Restaurant Owner3 Name, Restaurant Owner 2 Name, Restaurant Owner 1 Name

[Check] Check Identifier, Granuity Amount, Check Amount, Check Date and Time

[Booth] Booth Type Text, Smoking Section Indicator, Booth Capacity, Booth Location, Booth Location Code,
Booth Type Code

Relations
Dessert M Offer N Restaurant
Booth N – contains M Dessert
Restaurant 1 Process M Check
Restaurant 1 Contain N Booth
Check M Generate 1 Booth
Dessert 1 Contain N Check
Dessert N has M date
Each Dessert may have different offered date range attributes. As it violates the normalization rule, a new entity Date with attributes Date offered and Date range is added.


Hope this will help you.

Related Questions

Navigate

• Browse (All)
• Browse D
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.