Consider a database application about the ingredients and nutrition of different food items available in

Canada. In terms of design, following are some major requirements:

$$ Application owner$/$administrator will add products to the database. For each product, name,

barcode, manufacturer, ingredients, stores on which it is sold, nutritional details are some basic

attributes: A nutritional detail contains the item and its amount.

o Ingredient information contains the item and its source. Application owner$/$administrator may want to interact with manufacturers time to time to collect

data on their products. Name, address, phone number, email are some basic attributes that must be

stored.

$$ Application owner$/$administrator may want to interact with stores time to time to collect data on

the products they sell. Name, address, phone number, email, store locations are some basic

attributes that must be stored.

$$ Consumers can also request to add a product by providing its partial or full information.

$$ No data is kept about consumers except email. Email will be kept only if the consumer requests

for it$.$

$$ Consumers are provided with the interface to perform all sorts of search on the products currently

stored in the database.

$$ Products are categorized as common dietary preferences such as vegan, vegetarian, halaal, kosher

etc.

Design an ER$/$EER schema diagram for this application and upload it to your submission on

Crowdmark. Discuss any assumptions you make, and then justify your EER design choices. You must

not add any additional attributes or data requirements.

Mapping from ER to Relational Model

Questions $3-4$ and $5-6$: $[(5+5)$ x $2$ Marks$]$

Write the SQL code to create relational schemas from questions $1-2$ in your MySQL database. To indicate

the success of these steps you need to perform following actions for each of the question:

$$ Attach the code$/$script for each schema creation separately, in a text file, to your submission on

Crowdmark in the space for Question $5$ and $6.$

$$ Show the list of tables $($there should be none$)$ before the above codes$/$scripts are executed using

$$SHOW TABLES$$ and upload the screenshot to your submission on Crowdmark $($space for

Q$3$ & $4).$ You may do this step only once and then reuse the image for both the questions.

$$ Run these codes$/$scripts on your MySQL server.

$$ Show the list of tables after the two schema creation codes$/$scripts are executed using $$SHOW

TABLES$$ and upload the screenshot to your submission on Crowdmark $($space for Q$3$ & $4).$

$$ DESCRIBE statement can be used to see the schema of a table as shown in the example below:Application owner$/$administrator may want to interact with manufacturers time to time to collect

data on their products. Name, address, phone number, email are some basic attributes that must be

stored.

$$ Application owner$/$administrator may want to interact with stores time to time to collect data on

the products they sell. Name, address, phone number, email, store locations are some basic

attributes that must be stored.

$$ Consumers can also request to add a product by providing its partial or full information.

$$ No data is kept about consumers except email. Email will be kept only if the consumer requests

for it$.$

$$ Consumers are provided with the interface to perform all sorts of search on the products currently

stored in the database.

$$ Products are categorized as common dietary preferences such as vegan, vegetarian, halaal, kosher

etc.

Design an ER$/$EER schema diagram for this application and upload it to your submission on

Crowdmark. Discuss any assumptions you make, and then justify your EER design choices. You must

not add any additional attributes or data requirements.

Mapping from ER to Relational Model

Questions $3-4$ and $5-6$: $[(5+5)$ x $2$ Marks$]$

Write the SQL code to create relational schemas from questions $1-2$ in your MySQL database. To indicate

the success of these steps you need to perform following actions for each of the question:

$$ Attach the code$/$script for each schema creation separately, in a text file, to your submission on

Crowdmark in the space for Question $5$ and $6.$

$$ Show the list of tables $($there should be none$)$ before the above codes$/$scripts are executed using

$$SHOW TABLES$$ and upload the screenshot to your submission on Crowdmark $($space for

Q$3$ & $4).$ You may do this step only once and then reuse the image for both the questions.

$$ Run these codes$/$scripts on your MySQL server.

$$ Show the list of tables after the two schema creation codes$/$scripts are executed using $$SHOW

TABLES$$ and upload the screenshot to your submission on Crowdmark $($space for Q$3$ & $4).$

$$ DESCRIBE statement can be used to see the schema of a table as shown in the example below: DESCRIBE all the tables from the two questions, one by one, and upload the screenshots

to your submission on Crowdmark. Do this separately for each question and upload the

screenshots to the appropriate question space $($space for Q$3$ & $4).$

NOTE: Since these two questions involve several images$/$screenshots$,$