Supermarket

Task

You have been asked to create a database for a supermarket storehouse. The database will have to store information about all the products sold by the supermarket (code, description, section where the product is on display, number of items in stock). The sections are: dairy products, canned goods, frozen foods, household items, paper products.

Design an entity-relationship diagram, a relational schema and an instance of the database.

Create also the queries which will return:

  1. a list of all the items currently in stock (code and description).
  2. codes, descriptions, stock of all the products in the household items section.
  3. the descriptions of all the products of the frozen foods section and the paper products section which are not currently in stock.
  4. the ids of all the items that have a description which does not equal "canned tuna".
  5. all of the data relating to the item which corresponds to the id 1.