The assignment:

1. Download and install Postgres.

2. Create a database or use the default database called postgres

3. Create a script create.sql to define the tables food, product and madefrom. The food table represents general food, so it includes nutritional information about simple foods like onions or chicken wings as well as manufactured food products like a pasta sauce or buffalo wings. The product table is for additional information about the latter group. The madefrom table represents the many-to-many relationship between product and food.

1. Remember that SQL is code, even if the syntax is new for you. Do not use Word or another word processor to edit it. The Query Tool feature of pgAdmin4 has syntax highlighting.

1. Execute your scripts to test them.

2. The fields for the food table are: (unless noted so, fields may not be null)

1. id : a SERIAL to distinguish foods from each other

2. name : a string for a common name for the food, or a specific product name

3. type : a string for the type of food (fruit, vegetable, meat, grain, dairy)

4. size_fam: the familiar size of the food, like 1 cup

5. size_metric: the metric size of the food, a number

6. size_unit: the metric unit being used to measure the food, like grams

7. calories: the number of calories in one serving

8. total_fat: the grams of total fat in one serving

9. sat_fat: the grams of saturated fat in one serving

10. trans_fat: the grams of trans fat in one serving

11. cholesterol: the grams of cholesterol in one serving

12. sodium: the grams of sodium in one serving

13. total_carb: the total grams of carbohydrates in one serving

14. fiber: the grams of dietary fiber in one serving

15. total_sugar: the grams of any sugar in one serving

16. added_sugar: the grams of added sugar in one serving

17. protein: the grams of protein in one serving

3. For the product table, the attributes are:

1. foodId : the id of the product, a foreign key to the food table

2. madeBy : for now, this is just the name of the company that makes this product

3. size: the total size of the product (a number)

4. unit: the units of the size, like ounces or grams or count (if we are just counting how many there are)

5. cat : the category of the product, like dessert, entre, side dish, appetizer may be null

4. For the madefrom table, the attributes are:

1. prodID: the id of the product, a foreign key to the product table

2. ingredID: the id of the food that is part of the product, a foreign key to the food table (yes, this can be itself a product)

2. Question Create a script insert.sql to insert acceptable data. Use INSERT statements to insert records into the above tables

1. Insert enough records so that any queries will return at least one, but not all records of the table

2. SQL itself does not have the kind of variables like Python or C++ does, so do not try to separate an insertion into multiple statements

1. [Databases like Postgres do support stored procedures for more complicated operations, but that is a separate language]

3. Create a script query.sql that includes semicolon-separated queries to do the following (terminate each query with a semicolon so I can run the whole script at once. While running the whole script inside pgAdmin4 only shows the result of the last query, running it from the command-line using the psql tool shows all results) :

1. List the familiar serving size and nutritional information calories through protein- for one serving of all dessert products.

2. List the number of servings in a specific product (based on its total size and the serving size) pick a product whose total size is more than one serving this and query 4 allow you to use literal values for the specific parts of the question

3. List the names of products which do not have any dairy ingredients

4. List the amount of added sugar and names of products made by a specific company that have some added sugar, sorted by most to least added sugar

5. Display the first 3 meals that satisfy a calorie limit, based on the standard serving size. A meal will include a fruit and two products: a side-dish and an entree

FYI Need to write SQL script for create, insert and query as per the instructions.