1. The ReallyGoodRestaurant has decided to open a small shop beside the restaurant to sell high quality artisan cooking ingredients. The restaurant uses several suppliers to provide good ingredients. Each supplier supplies an ingredient in only one unit size e.g. supplier A supplies sea salt in bags of 500 grams and supplier B supplies sea salt in 250 gram bags. Each supplier charges a specific price for an ingredient in that unit size. However different suppliers can provide the same ingredient in different unit sizes. Also to encourage the sale of these ingredients, the restaurant provides a text description of the recipes used in the restaurant and indicates which ingredients from each recipe can be bought from the shop. Each recipe has been designed by one or more chefs in the restaurant. The ReallyGoodRestaurant needs to maintain a database capable of storing information about ingredients, the suppliers of those ingredients, the unit size that each supplier supplies a particular ingredient, a text description of each recipe, the name(s) of the chef(s) who designed each recipe, an indication of which ingredients are used in which recipes, the cost (to the restaurant) of ingredients in a particular unit sizes from suppliers, and the price for which the shop sells each ingredient (in a particular unt size). (i) Develop an Entity Relational (ER) Model for the above database, stating any assumptions you make. (ii) Using the appropriate mapping techniques, map the ER Model to a Relational Model and show the functional dependency between attributes within each table. In your answer, identify the Primary and Foreign keys for each relation.
1. The ReallyGoodRestaurant has decided to open a small shop beside the restaurant to sell high quality artisan cooking ingredients. The restaurant uses several suppliers to provide good ingredients. Each supplier supplies an ingredient in only one unit size e.g. supplier A supplies sea salt in bags of 500 grams and supplier B supplies sea salt in 250 gram bags. Each supplier charges a specific price for an ingredient in that unit size. However different suppliers can provide the same ingredient in different unit sizes. Also to encourage the sale of these ingredients, the restaurant provides a text description of the recipes used in the restaurant and indicates which ingredients from each recipe can be bought from the shop. Each recipe has been designed by one or more chefs in the restaurant. The ReallyGoodRestaurant needs to maintain a database capable of storing information about ingredients, the suppliers of those ingredients, the unit size that each supplier supplies a particular ingredient, a text description of each recipe, the name(s) of the chef(s) who designed each recipe, an indication of which ingredients are used in which recipes, the cost (to the restaurant) of ingredients in a particular unit sizes from suppliers, and the price for which the shop sells each ingredient (in a particular unt size). (i) Develop an Entity Relational (ER) Model for the above database, stating any assumptions you make. (ii) Using the appropriate mapping techniques, map the ER Model to a Relational Model and show the functional dependency between attributes within each table. In your answer, identify the Primary and Foreign keys for each relation.
Computer Networking: A Top-Down Approach (7th Edition)
7th Edition
ISBN:9780133594140
Author:James Kurose, Keith Ross
Publisher:James Kurose, Keith Ross
Chapter1: Computer Networks And The Internet
Section: Chapter Questions
Problem R1RQ: What is the difference between a host and an end system? List several different types of end...
Related questions
Question
ER diagrams, Information management,
Transcribed Image Text:CSU34041
1. The ReallyGoodRestaurant has decided to open a small shop beside the restaurant to sell
high quality artisan cooking ingredients. The restaurant uses several suppliers to provide
good ingredients. Each supplier supplies an ingredient in only one unit size e.g. supplier A
supplies sea salt in bags of 500 grams and supplier B supplies sea salt in 250 gram bags.
Each supplier charges a specific price for an ingredient in that unit size. However different
suppliers can provide the same ingredient in different unit sizes.
Also to encourage the sale of these ingredients, the restaurant provides a text description
of the recipes used in the restaurant and indicates which ingredients from each recipe can
be bought from the shop. Each recipe has been designed by one or more chefs in the
restaurant.
The ReallyGoodRestaurant needs to maintain a database capable of storing information
about ingredients, the suppliers of those ingredients, the unit size that each supplier
supplies a particular ingredient, a text description of each recipe, the name(s) of the
chef(s) who designed each recipe, an indication of which ingredients are used in which
recipes, the cost (to the restaurant) of ingredients in a particular unit sizes from
suppliers, and the price for which the shop sells each ingredient (in a particular unt size).
Develop an Entity Relational (ER) Model for the above database, stating any
assumptions you make.
(i)
Using the appropriate mapping techniques, map the ER Model to a Relational
Model and show the functional dependency between attributes within each
table. In your answer, identify the Primary and Foreign keys for each relation.
(ii)
(ii)
Give the SQL command(s) for the following queries:
(a). Retrieve a list of ingredients sold by the shop for the recipe 'Baked
Lasagne'
(b). One of the existing suppliers (named 'Honest Henry') has a new ingredient
(roasted chestnuts in units size of 400 grams) to be sold in the shop. The
shop buys this ingredient €3 per 400 gram bag and the shop sells it for €8
per (400 gram bag). Give the SQL commands needed to enter this new
ingredient.
(c). Create a view of all the ingredients of supplier 'Honest Henry' with the
unit size he supplies and the price he charges for that unit size.
Expert Solution
This question has been solved!
Explore an expertly crafted, step-by-step solution for a thorough understanding of key concepts.
This is a popular solution!
Trending now
This is a popular solution!
Step by step
Solved in 3 steps with 1 images
Recommended textbooks for you
Computer Networking: A Top-Down Approach (7th Edi…
Computer Engineering
ISBN:
9780133594140
Author:
James Kurose, Keith Ross
Publisher:
PEARSON
Computer Organization and Design MIPS Edition, Fi…
Computer Engineering
ISBN:
9780124077263
Author:
David A. Patterson, John L. Hennessy
Publisher:
Elsevier Science
Network+ Guide to Networks (MindTap Course List)
Computer Engineering
ISBN:
9781337569330
Author:
Jill West, Tamara Dean, Jean Andrews
Publisher:
Cengage Learning
Computer Networking: A Top-Down Approach (7th Edi…
Computer Engineering
ISBN:
9780133594140
Author:
James Kurose, Keith Ross
Publisher:
PEARSON
Computer Organization and Design MIPS Edition, Fi…
Computer Engineering
ISBN:
9780124077263
Author:
David A. Patterson, John L. Hennessy
Publisher:
Elsevier Science
Network+ Guide to Networks (MindTap Course List)
Computer Engineering
ISBN:
9781337569330
Author:
Jill West, Tamara Dean, Jean Andrews
Publisher:
Cengage Learning
Concepts of Database Management
Computer Engineering
ISBN:
9781337093422
Author:
Joy L. Starks, Philip J. Pratt, Mary Z. Last
Publisher:
Cengage Learning
Prelude to Programming
Computer Engineering
ISBN:
9780133750423
Author:
VENIT, Stewart
Publisher:
Pearson Education
Sc Business Data Communications and Networking, T…
Computer Engineering
ISBN:
9781119368830
Author:
FITZGERALD
Publisher:
WILEY